March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to 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.
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
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 |
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.
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"
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...
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |