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
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.
Community ChampionHi 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