The dax does not give the correct result

[sakuragihana]

Hello everyone ,

I have a dax to count teacher days by contract type : part time and full time . However, this dax function counts the number of duplicated days because there are many teaching shifts in that day.

 The visual is :

However, the correct result must be :

I post this dax in this Power BI file : https://drive.google.com/file/d/19gQEEb3nstLy5d41P34exgSSfeent7r3/view?usp=sharing 

Please help me to fix that measure. Thank you a lot !

[Idrissshatila]

Hello @sakuragihana ,

try the distinct  count function in dax rather than count, here is the syntax DISTINCTCOUNT(<column>)

https://learn.microsoft.com/en-us/dax/distinctcount-function-dax

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[sakuragihana]

Hi @Idrissshatila ,

I try it, but don know how to use DISTINCOUNT  for this matter.

[Idrissshatila]

Hello @sakuragihana ,

this documentation from microsoft shows how to use distinctcount 

https://learn.microsoft.com/en-us/dax/distinctcount-function-dax

distinct count by SQLBI https://www.sqlbi.com/articles/analyzing-distinctcount-performance-in-dax/#:~:text=DISTINCTCOUNT%20r....

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[sakuragihana]

Hi @Idrissshatila ,

This is my dax :

CountDayTeachFullTime =
VAR teachcode =
SELECTEDVALUE ( 'Teacher Contract'[TeacherCode] )
VAR fullTimeContracts =
FILTER (
'List Class Schedule Teacher',
'List Class Schedule Teacher'[TeacherCode] = teachcode
&& 'List Class Schedule Teacher'[TeacherContractType] = "Full Time"
)
VAR SelectedWeekStart =
MIN ( 'List Class Schedule Teacher'[Date] )
VAR SelectedWeekEnd =
MAX ( 'List Class Schedule Teacher'[Date] )
VAR SelectedStarttime =
SELECTEDVALUE ( 'List Class Schedule Teacher'[StartTime] )
RETURN
COUNTX (
fullTimeContracts,
VAR datemincontract =
MINX (
FILTER ( 'Teacher Contract', 'Teacher Contract'[TeacherCode] = teachcode ),
'Teacher Contract'[Start Date]
)
VAR datemaxcontract =
MAXX (
FILTER ( 'Teacher Contract', 'Teacher Contract'[TeacherCode] = teachcode ),
'Teacher Contract'[End Date]
)
RETURN
SWITCH (
WEEKDAY ( 'List Class Schedule Teacher'[Date], 2 ),
1,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
2,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
3,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
4,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
5,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
6,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
7,
IF (
'List Class Schedule Teacher'[Date] >= SelectedWeekStart
&& 'List Class Schedule Teacher'[Date] <= SelectedWeekEnd
&& 'List Class Schedule Teacher'[Date] >= datemincontract
&& 'List Class Schedule Teacher'[Date] <= datemaxcontract,
1,
0
),
BLANK ()
)
)

-------------------

The function DISTINCTCOUNT can not use for this matter. Can you help me ?

[sakuragihana]

Hi @Idrissshatila ,

This is my dax :

CountDayTeachFullTime =

VAR teachcode = SELECTEDVALUE('Teacher Contract'[TeacherCode])

VAR fullTimeContracts =

    FILTER('List Class Schedule Teacher', 'List Class Schedule Teacher'[TeacherCode] = teachcode && 'List Class Schedule Teacher'[TeacherContractType] = "Full Time")

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

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

VAR SelectedStarttime = SELECTEDVALUE('List Class Schedule Teacher'[StartTime])

RETURN

    COUNTX(

        fullTimeContracts,

        VAR datemincontract =

        MINX(

        FILTER ( 'Teacher Contract','Teacher Contract'[TeacherCode] = teachcode ),

        'Teacher Contract'[Start Date]

                                    )

        VAR datemaxcontract =

         MAXX (

        FILTER ( 'Teacher Contract','Teacher Contract'[TeacherCode] = teachcode  ),

        'Teacher Contract'[End Date]

    )

        RETURN

            SWITCH(

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

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

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

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

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

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

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

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

                BLANK()

            )

    )

So distinctcount can not use for this matter