Count distinct person only on the basis of highest value in other coloumn

[tobiasreisaeter]

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

[Anonymous · Accepted solution]

Hi @tobiasreisaeter

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