Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I'm having an issue with counting the number of disinct persons based on the grades they have received. I have a fact that contains the persons ID, their grade8s) and the quarter in which they received it. With DAX I would like to only return the highest grade, and hence only count the person once in a given quarter (given that the person received more than one grade in a quarter).
PersonID Grade Quarter
1 4 1
1 7 2
1 10 2
2 4 2
3 10 1
3 12 1
The result should be that if I use a pivot with PersonID as rows and Grade as coloumn the measure should filter out the lowest grade(s) and only display the higest one:
PersonID Grade Quarter
1 10 2
2 4 2
3 12 1
Could anyone please help with this?
Kind regards
Tobias
Solved! Go to Solution.
Create this measure
Max Grade = VAR inMax =CALCULATE(MAX(Table1[Grade]),ALLEXCEPT(Table1,Table1[PersonID])) RETURN IF(inMax= MAX(Table1[Grade]),1,0)
and then apply a visual filter on this measure to filter for values 1 , you will get teh desired result.
Thanks
Raj
Create this measure
Max Grade = VAR inMax =CALCULATE(MAX(Table1[Grade]),ALLEXCEPT(Table1,Table1[PersonID])) RETURN IF(inMax= MAX(Table1[Grade]),1,0)
and then apply a visual filter on this measure to filter for values 1 , you will get teh desired result.
Thanks
Raj
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |