Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
hamtuv
Regular Visitor

Map names to number hierarchy

I have a table with a column called Accounts that have a 6 digit number where each digit represents a group, for example 102211 can be split into 1 (head group), 0 (sub-group), 2 (sub-group) and so on. I have split this column into different column to create a hierarchy so that I can drill down into each group in a matrix table. I then have a different table that acts as a "name-map" for the groups, for example 1 = Cash in, 10 = Group 1, 102 = Group 3 and so on. It's split into the numbers, names and then numbers and names combined. Is there a way to display the number and names in the matrix table with these parameters? I still want the "drill down" effect in the table.

Regards Hampus 

1 ACCEPTED SOLUTION

Hi, @hamtuv 

 

You can try the following methods.

Column:

AccountName = CALCULATE(MAX('The Account map'[AccountName]),FILTER('The Account map',[Account]=EARLIER('Table'[Account])))
AccountName1 = "1 Cash in"

vzhangtinmsft_0-1727158307452.png

vzhangtinmsft_1-1727158327767.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Ritaf1983
Super User
Super User

Hi @hamtuv 

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

The table with the accounts

AccountValue
100210
100312
100410

 

The Account map

AccountNameAccountName
1Cash in1 Cash in
10Group 110 Group 1
100Group 2 100 Group 2
1002Group 31002 Group 3
1003Group 41002 Group 4
1004Group 51002 Group 5

 

I then want a matrix table that starts at 1, then you should be able to click the plus sign and it should drill down to 10, then 100 and so on. 
Note it should be a matrix table with + signs that you can open more rows

AccountValue
1 Cash in32
10 Group 132
100 Group 232
1002 Group 310
1003 Group 412
1004 Group 510

 

Hi, @hamtuv 

 

Did you expect this kind of output? 

vzhangtinmsft_0-1727072726570.png

The source data needs to be in this format.

vzhangtinmsft_1-1727072768182.png

Are you needing your the Account map table to morph to such data?

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Yes but with the group names next to the account numbers, either in the same column or one next to the numbers, so in the example that you sent row 1 would be 1 Cash In, row 2 would be 10 Group 2 and so on.

Hi, @hamtuv 

 

You can try the following methods.

Column:

AccountName = CALCULATE(MAX('The Account map'[AccountName]),FILTER('The Account map',[Account]=EARLIER('Table'[Account])))
AccountName1 = "1 Cash in"

vzhangtinmsft_0-1727158307452.png

vzhangtinmsft_1-1727158327767.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @hamtuv 
Sorry, but I don't understand how the first table relates to the last one...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.