Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tobiasreisaeter
Frequent Visitor

Count distinct person from other value

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

 

7 REPLIES 7
v-lili6-msft
Community Support
Community Support

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")

5.png

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

6.png

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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).

Capture.PNG

 

 

@AnonymousOoops forgot to tag you.

Anonymous
Not applicable

@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]))

 

Capture.PNG

 

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). 

Anonymous
Not applicable

@tobiasreisaeter can you post your formula as well

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.