The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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)) )
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)) )
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 🙂
User | Count |
---|---|
75 | |
70 | |
39 | |
30 | |
28 |
User | Count |
---|---|
104 | |
95 | |
51 | |
48 | |
46 |