topic Re: My dax is not working correctly in DAX Commands and Tips

My dax is not working correctly

sakuragihana — Mon, 24 Jul 2023 09:10:38 GMT

Hello everyone, I have a dax for counting the number of days for Monday, Tuesday, Wednesday, Thursday, Friday, Saturday Sunday at the month.

DayOfTeachCount =

VAR SelectedWeekStart = MIN ('Teacher Schedule'[Date])

VAR SelectedWeekEnd = MAX ('Teacher Schedule'[Date])

VAR Starttime = VALUE(SELECTEDVALUE('Teacher Schedule'[StartTime]))

VAR Endtime = VALUE(SELECTEDVALUE('Teacher Schedule'[EndTime]))

VAR Result =

SUMX(

VALUES ( 'Teacher Schedule'[Date]),

SWITCH (

WEEKDAY ( 'Teacher Schedule'[Date],2 ),

1, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

2, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

3, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

4, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

5, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

6, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

7, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1, 0 ),

BLANK ()

)

Return Result

But the visual is :

But the DayofTeachCount is not exactly because 3 of Fridays are not time for teaching

The visual right must have be : DayofTeachRight = 10

I make a power BI file with table data in this link:https://drive.google.com/file/d/1KRmfetcp4tVrf2Xti2HMZRFnMJELpvTU/view?usp=drive_link

Please everyone help me add a condition into dax DayOfTeachCount : if StartTime > 0 and EndTime > 0 to count the number of days of Monday, Tuesday, ..., if StartTime = 0 and EndTime = 0 then the day is not counted.

Thank a lot

Re: My dax is not working correctly

mlsx4 — Mon, 24 Jul 2023 11:21:23 GMT

Hi sakuragihana

You can create a kind of multiplier:

Multiplier = SUMX('Teacher Schedule', IF('Teacher Schedule'[StartTime]="0",0,1))

And then, modify your measure:

DayOfTeachCount = VAR SelectedWeekStart = MIN ('Teacher Schedule'[Date]) VAR SelectedWeekEnd = MAX ('Teacher Schedule'[Date]) VAR Starttime = VALUE(SELECTEDVALUE('Teacher Schedule'[StartTime])) VAR Endtime = VALUE(SELECTEDVALUE('Teacher Schedule'[EndTime])) VAR Result = SUMX( VALUES ( 'Teacher Schedule'[Date]), SWITCH ( WEEKDAY ( 'Teacher Schedule'[Date],2), 1, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), 2, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), 3, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), 4, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), 5, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), 6, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), 7, IF ( 'Teacher Schedule'[Date] >= SelectedWeekStart && 'Teacher Schedule'[Date] <= SelectedWeekEnd, 1*[Multiplier], 0 ), BLANK () ) ) Return Result

Re: My dax is not working correctly

sakuragihana — Tue, 25 Jul 2023 01:32:06 GMT

Hi mlsx4 ,

I am sorry but the dax is not correctly. The result is : 21 ( the right result is 10)

Re: My dax is not working correctly

mlsx4 — Tue, 25 Jul 2023 06:22:00 GMT

But why 10? Because of Saturdays and Sundays?

Re: My dax is not working correctly

sakuragihana — Tue, 25 Jul 2023 08:55:48 GMT

Hi mlsx4 ,

You can see data in power BI file , the Friday that teacher have no schedule .

Re: My dax is not working correctly

mlsx4 — Tue, 25 Jul 2023 08:58:47 GMT

Hi sakuragihana

But I don't understand your point. It is not taking into account the fridays:

Re: My dax is not working correctly

sakuragihana — Tue, 25 Jul 2023 09:53:01 GMT

Hi mlsx4 ,

Total DayofTeach = 10 ( because Friday is not teach) , and another is count distinct because at one day have many time for teaching .