Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PipSqueak
Frequent Visitor

How to fix totals in filtered measures

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.

 

2018-05-25 12_07_30-Untitled - Power BI Desktop.png

 

 

Thanks

Regards

 

 

1 ACCEPTED SOLUTION
Phil_Seamark
Microsoft Employee
Microsoft Employee

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))
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Microsoft Employee
Microsoft Employee

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))
        )

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes! Thank you so much!!

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))
        )

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 🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.