Map names to number hierarchy

[hamtuv]

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 

[Ritaf1983]

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  

[hamtuv]

The table with the accounts

Account	Value
1002	10
1003	12
1004	10

The Account map

Account	Name	AccountName
1	Cash in	1 Cash in
10	Group 1	10 Group 1
100	Group 2	100 Group 2
1002	Group 3	1002 Group 3
1003	Group 4	1002 Group 4
1004	Group 5	1002 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

Account	Value
1 Cash in	32
10 Group 1	32
100 Group 2	32
1002 Group 3	10
1003 Group 4	12
1004 Group 5	10

[v-zhangtin-msft]

Hi, @hamtuv 

Did you expect this kind of output? 

The source data needs to be in this format.

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.

[hamtuv]

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.

[v-zhangtin-msft · 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"

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.

[Ritaf1983]

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