Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All! I have data structured in the following format:
ID | Type | Value |
100000000 | Person | John Doe |
100000000 | Address | 123 Main Street |
100000000 | Phone Number | 777-777-4444 |
100000001 | Person | Jane Doe |
100000001 | Address | 123 Main Street |
100000002 | Person | John Doe |
100000002 | jdoe@abc.com |
I'd like to either create a new table via Transform or a measure that organizes the data by Person:
Name | Count of ID | Related Type |
John Doe | 2 | Address |
John Doe | 2 | Phone Number |
John Doe | 2 | |
Jane Doe | 1 | Address |
Essentially, I'd like to Group By Name and pull in all the Related Types where the ID match that name. Been stuck for hours and can't seem to figure it out.
Thanks!
Hi @Arete ,
Ranking measures is currently not possible because measures are data generated in real time.
You can try clicking sort and then reading the top 10 names.
Best Regards,
Gallen Luo
Hi @Arete ,
Please follow these steps:
(1) Create a new column
Name = MAXX( FILTER('Table','Table'[ID]=EARLIER('Table'[ID]) && 'Table'[Type] = "Person"),'Table'[Value])
(2) Create new measure
Count of ID = CALCULATE(DISTINCTCOUNT('Table'[ID]),ALL('Table'),'Table'[Name] = MAX('Table'[Name]))
FLAG = IF(MAX('Table'[Type] )= "Person" ,0,1)
(3)Apply filtering
(4)Final output
Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow, thanks for this. There is a lot of data and it's taking forever to load. If I wanted to filter it to the Top 10 people by Count would you suggest I do so in the Flag measure or the calculated column? I believe I should use TopN but not entirely sure how to modify your DAX.
@v-jialluo-msft
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |