Forum Discussion
Need Help with Dax
Hello
I have a table "Meeting" with , for all Employee, a start and a end date appointment
Employee Date_Start Date_End
E1 01/01/2017 09:00:00 01/01/2017 15:00:00
E1 02/01/2017 09:00:00 03/01/2017 17:00:00
E2 02/01/2017 11:00:00 02/01/2017 12:00:00
In Power BI, I want a table or a matrix, who each day, give me the disponibility of the employees hour by hour (between 8h and 19h)
Example: Filter : 01/01/2017
| Employee | 8h | 9h | 10h | 11h | 12h | 13h | 14h | 15h | 16h | 17h | 18h |
| E1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
| E2 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
Filter 02/01/2017
| Employee | 8h | 9h | 10h | 11h | 12h | 13h | 14h | 15h | 16h | 17h | 18h |
| E1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| E2 | 1 | 1 | 1 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
And which Mesure/Calculated Column should I must create ?
Should I must create a date dimension with all the day and hour ? But if an employee had no meeting, he must appear in the table.
Warning : an appointment can start one day and finish the next day or more.... (Employee E1 - row 2)
Thanks a lot for your answer
Hi Vincent112358,
You need to import an extra table like below. [Hour] is whole number and [HourTime] in text. Please sort [HourTime] based on [Hour] field.
Open Query Editor mode.
1. Add two custom date columns in 'Meeting' table.
StartDate=DateTime.Date([Date_Start]) EndDate=DateTime.Date([Date_End])
2. Change these two new columns' data type to whole number.
3. Add a custom column named as [DateRange] like below. Then, expand this new column. After expanding, remember to change data type of [StartDate], [EndDate] and [DateRange] from numeric to date.
4. Add two custom columns which return the start hour and end hour per day.
StartTime=if [DateRange]>DateTime.Date([Date_Start]) then 8 else Time.Hour([Date_Start]) EndTime=if [DateRange]<DateTime.Date([Date_End]) then 18 else Time.Hour([Date_End])-1
5. Similar to above step3, create a list column [TimeRange] then expand it.
TimeRange={[StartTime]..[EndTime]}Save all above changes and close. Now, in data view mode, please refer to below steps.
1. Create a calculated table named as 'New Meeting' using below DAX formula.
New Meeting = UNION ( SELECTCOLUMNS ( Meeting, "Employee", Meeting[Employee], "Date", Meeting[DateRange], "Hour", Meeting[TimeRange] ), ADDCOLUMNS ( CROSSJOIN ( VALUES ( Meeting[Employee] ), VALUES ( Meeting[DateRange] ) ), "Hour", BLANK () ) )
2. Create a one to many relationship between 'Hour Table' and 'New Meeting' based on [Hour] field.
3. Create a measure to check whether an employee has meeting at specific time.
IsMeeting = IF ( LASTNONBLANK ( 'New Meeting'[Hour], 1 ) = LASTNONBLANK ( 'Hour Table'[Hour], 1 ), 0, 1 )
4. Now, to create your report, add 'New Meeting'[Date] into slicer. Drag corresponding fields into Matrix visual. Please pay attention to the highlighted section in below image.
I have uploaded the sample .pbix file for your reference.
Best regards,
Yuliana Gu
2 Replies
Hi Vincent112358,
You need to import an extra table like below. [Hour] is whole number and [HourTime] in text. Please sort [HourTime] based on [Hour] field.
Open Query Editor mode.
1. Add two custom date columns in 'Meeting' table.
StartDate=DateTime.Date([Date_Start]) EndDate=DateTime.Date([Date_End])
2. Change these two new columns' data type to whole number.
3. Add a custom column named as [DateRange] like below. Then, expand this new column. After expanding, remember to change data type of [StartDate], [EndDate] and [DateRange] from numeric to date.
4. Add two custom columns which return the start hour and end hour per day.
StartTime=if [DateRange]>DateTime.Date([Date_Start]) then 8 else Time.Hour([Date_Start]) EndTime=if [DateRange]<DateTime.Date([Date_End]) then 18 else Time.Hour([Date_End])-1
5. Similar to above step3, create a list column [TimeRange] then expand it.
TimeRange={[StartTime]..[EndTime]}Save all above changes and close. Now, in data view mode, please refer to below steps.
1. Create a calculated table named as 'New Meeting' using below DAX formula.
New Meeting = UNION ( SELECTCOLUMNS ( Meeting, "Employee", Meeting[Employee], "Date", Meeting[DateRange], "Hour", Meeting[TimeRange] ), ADDCOLUMNS ( CROSSJOIN ( VALUES ( Meeting[Employee] ), VALUES ( Meeting[DateRange] ) ), "Hour", BLANK () ) )
2. Create a one to many relationship between 'Hour Table' and 'New Meeting' based on [Hour] field.
3. Create a measure to check whether an employee has meeting at specific time.
IsMeeting = IF ( LASTNONBLANK ( 'New Meeting'[Hour], 1 ) = LASTNONBLANK ( 'Hour Table'[Hour], 1 ), 0, 1 )
4. Now, to create your report, add 'New Meeting'[Date] into slicer. Drag corresponding fields into Matrix visual. Please pay attention to the highlighted section in below image.
I have uploaded the sample .pbix file for your reference.
Best regards,
Yuliana GuHi Yuliana,
Thanks a lot for your answer. I try your solution with my data and it's OK.
I had only an error:
It's because 'New Meeting'[HOUR]' type is "Text" and not "Whole Number" like you.
When I try to change the type, I have this message error :
So, I just change the measure IsMeeting :
IsMeeting = IF(VALUE(LASTNONBLANK('New Meeting'[Hour];1))=LASTNONBLANK('Hour'[Hour];1);0;1)
Now, I must try this dashboard with a lot of data.
Thanks again
Best Regards
Vincent Guichard
