Forum Discussion
Grouping Items
Hi guys, thank you again for your help!
A bloody beginner, I have another question.
I would like to illustrate revenues that are associated with ICD codes. These codes are structured in the following way
A00-B99: Certain infectious and parasitic diseases
A00-A09: Intestial infectious diseases
A00: Cholera
A00.0 Cholera due to X
A00.1 Cholera due to Y
A01: Typhoid and paratyphoid fevers
A01.0 Typhoid fever
A01.1 Paratyphoid fever A
A01.2 Paratyphoid fever B
A01.3 Paratyphoid fever C
(.....)
C00-D48: Neoplasms
(.....)
Problem is, my table contains thousands of ICD10 Codes (see screenshot below) on different hierarchies and I would like to group these ICD10 codes according to the three highest hierarchy levels (marked in red, blue and green in the above extract). Does anyone know how to achieve this?
THanks so much!
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
5 Replies
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 ForumHello 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
Anonymous , do you have a level which can be identified by the number or by Path length or by rank.
Then you can create a color measure and use that
Refer path here
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415Color measure you can create like
Color Date = if(FIRSTNONBLANK('Date'[Date],TODAY()) <today(),"lightgreen","red") Color Date = var _min =minx(allselected(Date,Date[Year]) return Switch( true(), FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"lightgreen", FIRSTNONBLANK('Date'[Year],year(TODAY()))-_min =0 ,"blue", "red") if(FIRSTNONBLANK(Table[Value],"true")= "true","green","red")How to use
https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesHi Anonymous
You can build a table as below, then you can use Drill up and Drill down. For more info: Drill mode in a visual in Power BI
Result:
You can download the pbix file from this link:Grouping Items
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
