Forum Discussion
Grouping Items
Hi Anonymous ,
That really doesn't help me too much, but only because I do not understand your data. You are saying that is different heirarchies in one column, but I can only possibly make out 2, and may be guessing wrong at that.You are going to have to reshape your data so there is a key at the lowest level in your FACT table, then use your DIM table to slice however you want. I created this dummy DIM table:
Then I took your original table, and merged it to level 1, 2, and 3 and expanded the Level one amount. It returned NULL for all but one of those merges, so then I did a "merge columns" on those levels. It now looks like this:
Then you would create a 1-Many relationship between the L1 field of the DIM table to the L1 field of the FACT table. See my PBIX attached.
If you need further help, you are going to have to provide a more comprehensive sample of data and explain the multiple leves and how your business defines those. But you must work this into a Start Schema as I have, or you will continue to hit dead ends in visuals and DAX measures.
Microsoft Guidance on Importance of Star Schema
How is your source data laid out? I'd need to see that. Looks like a good bit of modeling is going to be necessary. You need some DIM tables set up, which can be done, but I'd need to see the source data (fake of course). See links below on providing that.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
Hello edhans
please see below what my table looks like. I only incuded the relevant columns.
| Product | Revenue | ICD10 |
| Drug A | 2 | D86.9 |
| Drug A | 2 | T78.4 |
| Drug B | 1 | R11 |
Drug B | 1 | D86.9 |
| Drug C
| 3 | R11 |
So as you can see, all the ICDs on different hierarchy levels are in one column. Would be so appreciated if you could help.
Hi Anonymous ,
That really doesn't help me too much, but only because I do not understand your data. You are saying that is different heirarchies in one column, but I can only possibly make out 2, and may be guessing wrong at that.You are going to have to reshape your data so there is a key at the lowest level in your FACT table, then use your DIM table to slice however you want. I created this dummy DIM table:
Then I took your original table, and merged it to level 1, 2, and 3 and expanded the Level one amount. It returned NULL for all but one of those merges, so then I did a "merge columns" on those levels. It now looks like this:
Then you would create a 1-Many relationship between the L1 field of the DIM table to the L1 field of the FACT table. See my PBIX attached.
If you need further help, you are going to have to provide a more comprehensive sample of data and explain the multiple leves and how your business defines those. But you must work this into a Start Schema as I have, or you will continue to hit dead ends in visuals and DAX measures.
Microsoft Guidance on Importance of Star Schema