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
Arete
Helper I
Helper I

Data modeling help with related records

Hi All! I have data structured in the following format:

 

IDTypeValue
100000000PersonJohn Doe
100000000Address123 Main Street
100000000Phone Number777-777-4444
100000001PersonJane Doe
100000001Address123 Main Street
100000002PersonJohn Doe
100000002Emailjdoe@abc.com

 

I'd like to either create a new table via Transform or a measure that organizes the data by Person:

 

NameCount of IDRelated Type
John Doe2Address
John Doe2Phone Number
John Doe2Email
Jane Doe1Address

 

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!

3 REPLIES 3
v-jialluo-msft
Community Support
Community Support

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.

vjialluomsft_0-1672211560492.png

 

 

Best Regards,
Gallen Luo

v-jialluo-msft
Community Support
Community Support

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

vjialluomsft_0-1671175924041.png

(4)Final output

vjialluomsft_1-1671175949223.png

 

 

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 

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.