Need Help with Dax

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