How to fix totals in filtered measures

[PipSqueak]

Hi,

This is the code I'm using for the measure. It calculates the duration between the earliest and latest time in a day when a staff enters/exits the building. 

Duration = 
VAR hours = 
    CALCULATE(DATEDIFF(MIN(ReportEvent[Tagged]),MAX(ReportEvent[Tagged]), MINUTE)/60)
RETURN
    IF(HASONEFILTER(ReportEvent[Tagged - Copy.1]),hours,SUMX(ReportEvent, hours))

I can't seem to get the totals to add up - I'm totally new to DAX and had searched in the forums for possible solution, but I can't seem to get any of the codes I obtained to work. Any help will be appreciated.

Thanks

Regards

[Phil_Seamark · Accepted solution]

HI @PipSqueak

Is this any better?

Duration = 
VAR hours = 
    CALCULATE(DATEDIFF(MIN(ReportEvent[Tagged]),MAX(ReportEvent[Tagged]), MINUTE)/60)
RETURN
    IF(
        HASONEFILTER(ReportEvent[Tagged - Copy.1]),
        hours,
        SUMX(VALUES(ReportEvent[Tagged - Copy.1]),  CALCULATE(DATEDIFF(MIN(ReportEvent[Tagged]),MAX(ReportEvent[Tagged]), MINUTE)/60))
        )

[PipSqueak]

Yes! Thank you so much!!

[PipSqueak]

Curious, why doesn't it work the same if I used 

Duration = 
VAR hours = 
    CALCULATE(DATEDIFF(MIN(ReportEvent[Tagged]),MAX(ReportEvent[Tagged]), MINUTE)/60)
RETURN
    IF(
        HASONEFILTER(ReportEvent[Tagged - Copy.1]),
        hours,
        SUMX(VALUES(ReportEvent[Tagged - Copy.1]),  hours)
        )

Instead of:

Duration = 
VAR hours = 
    CALCULATE(DATEDIFF(MIN(ReportEvent[Tagged]),MAX(ReportEvent[Tagged]), MINUTE)/60)
RETURN
    IF(
        HASONEFILTER(ReportEvent[Tagged - Copy.1]),
        hours,
        SUMX(VALUES(ReportEvent[Tagged - Copy.1]),  CALCULATE(DATEDIFF(MIN(ReportEvent[Tagged]),MAX(ReportEvent[Tagged]), MINUTE)/60))
        )

[Phil_Seamark]

When you run the CALCULATE as part of the variable assignment, it assigns a value to the hours variable.

When you drop the hours variable into the SUMX, it will just sum that value over and over.

Whereas when you use the CALCULATE function in the SUMX, it calculates the appropriate value for each iteration of the SUMX function. 

I have a good book on Filters and Context in DAX if you want to learn more 🙂