Forum Discussion
Solved
Conditional distinct count dax
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..
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
2 Replies
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 __ResultDAX 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
