Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I have table like the below. I need to build a dynamic measure that will indicate if the total pump run hours per site for a given date is zero so that I can count or filter on sites with total zero hours.
Source table:
Site | Date | Pump | Run_Hours |
AAA | 01/01/2019 | 1 | 0 |
BBB | 01/01/2019 | 1 | 6 |
BBB | 01/01/2019 | 2 | 0 |
CCC | 01/01/2019 | 1 | 4 |
DDD | 01/01/2019 | 1 | 0 |
DDD | 01/01/2019 | 2 | 0 |
Desired output:
Site | Date | Zero_Hour_Sites |
AAA | 01/01/2019 | 1 |
BBB | 01/01/2019 | 0 |
CCC | 01/01/2019 | 0 |
DDD | 01/01/2019 | 1 |
Total | 2 |
I have found a similar requirement here where the proposed solution was something along the lines of:
Measure =
COUNTROWS (
DISTINCT ( CALCULATETABLE ( VALUES ( Table[Site] ), Table[Run Hours] = 0 ) )
)
But I am not sure how to enhance this to group by both Site and Date?
Many thanks.
Jeanette
Solved! Go to Solution.
@JBarrett3 Ok, please update the Measure logic as below
Test308 = VAR _Zero = DISTINCT(SELECTCOLUMNS(FILTER(Test308Filter,Test308Filter[Run_Hours]=0),"Site",[Site],"Date",[Date])) VAR _NonZero = DISTINCT(SELECTCOLUMNS(FILTER(Test308Filter,Test308Filter[Run_Hours]>0),"Site",[Site],"Date",[Date])) RETURN COUNTROWS(DISTINCT(EXCEPT(_Zero,_NonZero)))+0
Proud to be a PBI Community Champion
@JBarrett3 Please try this as New Measure.
Test308 = COUNTROWS(DISTINCT(EXCEPT(CALCULATETABLE(VALUES(Test308Filter[Site]),Test308Filter[Run_Hours]=0),CALCULATETABLE(VALUES(Test308Filter[Site]),Test308Filter[Run_Hours]>0))))+0
Proud to be a PBI Community Champion
Thank you so much.
That works great for 1 day, but when I introduce a second date the total no longer works. Any idea how to resolve that?
@JBarrett3 Ok, please update the Measure logic as below
Test308 = VAR _Zero = DISTINCT(SELECTCOLUMNS(FILTER(Test308Filter,Test308Filter[Run_Hours]=0),"Site",[Site],"Date",[Date])) VAR _NonZero = DISTINCT(SELECTCOLUMNS(FILTER(Test308Filter,Test308Filter[Run_Hours]>0),"Site",[Site],"Date",[Date])) RETURN COUNTROWS(DISTINCT(EXCEPT(_Zero,_NonZero)))+0
Proud to be a PBI Community Champion
User | Count |
---|---|
85 | |
85 | |
67 | |
61 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |