Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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..
Solved! Go to 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
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
@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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |