Forum Discussion

sakuragihana's avatar
sakuragihana
Icon for Helper IV rankHelper IV
3 years ago

My dax is not working correctly

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

6 Replies

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable Member
    3 years ago

    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
    • sakuragihana's avatar
      sakuragihana
      Icon for Helper IV rankHelper IV
      3 years ago

      Hi mlsx4 ,

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

       

       

       

      • mlsx4's avatar
        mlsx4
        Icon for Memorable Member rankMemorable Member
        3 years ago

        But why 10? Because of Saturdays and Sundays?