Count distinct person from other value

Question

Hi!I'm having an issue with counting&nbsp;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&nbsp;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).&nbsp;&nbsp;PersonID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Grade &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Quarter1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 11 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp;1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 23 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 13 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 12 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;&nbsp;The result should be that if I use a pivot with PersonID as rows and Grade as coloumn the&nbsp;measure should filter out the lowest grade(s) and only display the higest one:&nbsp;PersonID &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Grade &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Quarter1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 10 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 22 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 23 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 12 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Could anyone please help with this?&nbsp;Kind regards&nbsp;Tobias&nbsp;

anonymous · Answer

tobiasreisaeter&nbsp;use allexcept to find the highest grade per person&nbsp;calculate(max([grade]), allexcept(table1, table1[person id])&nbsp;here is the allexcept usage guidehttps://msdn.microsoft.com/en-us/query-bi/dax/allexcept-function-dax

tobiasreisaeter · Answer

Hello mnayar&nbsp;Thanks for the reply.&nbsp;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).&nbsp;&nbsp;

tobiasreisaeter · Answer

AnonymousOoops forgot to tag you.

anonymous · Answer

tobiasreisaeter&nbsp;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

anonymous · Answer

tobiasreisaeter&nbsp;can you post your formula as well

Forum Discussion

Count distinct person from other value

7 Replies

Related content

count distinct

Distinct Count

Distinct count

DAX Calculation for Distinct Count

Average - Distinct Count