Conditional distinct count dax

[MEHUL123]

Hi,

Request your help to create DAX for Distinct count of IDs against each Name rows in table.

(desired result shown in last colunms)

Name	ID NO.	DAX for Desired Result (Distinct ID against Name)
Rohan	AA	2
Rohan	BB	2
Rohan	AA	2
Mira	BB	2
John	AA	1
Mira	CC	2
Celvin	AA	1
James	AA	1
Celvin	AA	1

Thanks in advance..

[Kedar_Pande]

@MEHUL123 

DAX measure:

Distinct ID Count = 
CALCULATE(
DISTINCTCOUNT(Table[ID NO.]),
ALLEXCEPT(Table, Table[Name])
)

This will give you the distinct count of ID NO. for each Name.

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[Greg_Deckler · Accepted solution]

@MEHUL123 Try this. PBIX is attached below signature.

Distinct Count = 
    VAR __Name = MAX( 'Table'[Name] )
    VAR __Table = FILTER( ALLSELECTED( 'Table' ), 'Table'[Name] = __Name )
    VAR __Group = SUMMARIZE( __Table, [Name], [ID] )
    VAR __Result = COUNTROWS( __Group )
RETURN
    __Result