Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Hi@ tobiasreisaeter
After my research , You can do these follow my steps like below:
Step 1:
Add a column to mark which grade is the max for every personalID
Column = IF(Table1[Grade]= CALCULATE(MAX(Table1[Grade]),FILTER(Table1,Table1[PersonID]=EARLIER(Table1[PersonID]))),"Max")
Step 2:
Add this column into a slicer or visual level filters or page level filters to filter the data when you add other measure
Best Regards,
Lin
@tobiasreisaeter use allexcept to find the highest grade per person
calculate(max([grade]), allexcept(table1, table1[person id])
here is the allexcept usage guide
https://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax
Hello mnayar
Thanks for the reply.
Unfortunately, it didn't quite work. Seems as if it ignores the higest value for the individual person (and only puts 12 while still showing 12 for both the occurence of 7 and 18 for PersonID 1).
@AnonymousOoops forgot to tag you.
@tobiasreisaeter it is calculating 12 which is max per person so if you want it by person id and quarter then you need to add both of them in the allexcept
@AnonymousDidn't seem to to the job either. I'm just gonna send the formula below. And also what it looks like in powerpivot.
ALL EXCEPT:=CALCULATE(MAX('FACT'[Grade]);ALLEXCEPT('FACT';'FACT'[PersonID]; 'FACT'[Quarter]))
Still both grades for the individual ID is shown just as it should count just "1" for the highest grade and omit the other(s).
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |