Need help for fixing this dax of Sum (not include duplicated days)

[Simple_one]

Hello everyone,

I have a dax for calculating Standard Teaching Hours for teacher

Example : We have standard teaching hours according to each type of contract for weekdays

In this visual , my dax has a result

In this data :

But this total 53.64 is not correct because some day in the teacher Schedule is duplicated (one day with many shifts)

However , the correct result is  (standard time is not calculated the day with many shifts) :

I made a power BI file in this link : https://drive.google.com/file/d/1EkI4Cxe6zouBPcZZXbj0c4hRVnGJcpQy/view?usp=drive_link 

Please help me for fixing this dax  !

[Mahesh0016 · Accepted solution]

@Simple_one  I hope this helps you. Thank You!!

> I have created Two Measure like Below :

Distinct Time = 

SUMX(

    VALUES('List Class Schedule Teacher'[TimeStandard])

    ,'List Class Schedule Teacher'[TimeStandard]

)

> Total Time Standard =
VAR PRT =
CALCULATE (
SUMX ( VALUES ( 'List Class Schedule Teacher'[Date Key] ), [Distinct Time] ),
'List Class Schedule Teacher'[TeacherContractType] = "Part Time",
'List Class Schedule Teacher'[TeacherCode]
= SELECTEDVALUE ( 'List Class Schedule Teacher'[TeacherCode] )
)
VAR FLT =
CALCULATE (
SUMX ( VALUES ( 'List Class Schedule Teacher'[Date Key] ), [Distinct Time] ),
'List Class Schedule Teacher'[TeacherContractType] = "Full Time",
'List Class Schedule Teacher'[TeacherCode]
= SELECTEDVALUE ( 'List Class Schedule Teacher'[TeacherCode] )
)
RETURN
PRT + FLT

[Mahesh0016]

@Simple_one please can you give your logic about standard hours?
Because I was comparing the Measure result with the data so measure value is right because behind the data is 53.63. I filter out blank data so the total is 42.84.
Thank You!!

[Simple_one]

Hi @Mahesh0016 ,

Everyday has a standard Time as :

But in the data , if we sum the column Time Standard , the result is not correct because some day the teacher has many shifts ( Time Stadard for Saturday/ week is 1.6 hours for Part Time contract  ).

Example : The Saturday (17/06/2023) has 2 shifts ( if you see the raw data)

So the result is sum all of Time Standard ( not include duplicated day ) : 42.64.

Please help me to fix this . Thank you

[Mahesh0016]

@Simple_one Please can you share your Expected output in the table for better understanding? Thank You!!

[Simple_one]

Hi @Mahesh0016 ,

I want the output of Standard Teaching Time in visual is : 42.64

My dax is not correct. Can you help me fix it ?

[Mahesh0016 · Accepted solution]

@Simple_one  I hope this helps you. Thank You!!

> I have created Two Measure like Below :

Distinct Time = 

SUMX(

    VALUES('List Class Schedule Teacher'[TimeStandard])

    ,'List Class Schedule Teacher'[TimeStandard]

)

> Total Time Standard =
VAR PRT =
CALCULATE (
SUMX ( VALUES ( 'List Class Schedule Teacher'[Date Key] ), [Distinct Time] ),
'List Class Schedule Teacher'[TeacherContractType] = "Part Time",
'List Class Schedule Teacher'[TeacherCode]
= SELECTEDVALUE ( 'List Class Schedule Teacher'[TeacherCode] )
)
VAR FLT =
CALCULATE (
SUMX ( VALUES ( 'List Class Schedule Teacher'[Date Key] ), [Distinct Time] ),
'List Class Schedule Teacher'[TeacherContractType] = "Full Time",
'List Class Schedule Teacher'[TeacherCode]
= SELECTEDVALUE ( 'List Class Schedule Teacher'[TeacherCode] )
)
RETURN
PRT + FLT

[Simple_one · Accepted solution]

Hi @Mahesh0016,

Thank you so much !