Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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?

Forum2.jpg

THanks so much!

 

 

 

1 ACCEPTED SOLUTION

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:

edhans_0-1595880904443.png

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:

edhans_1-1595880980618.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

5 REPLIES 5
v-rzhou-msft
Community Support
Community Support

Hi @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

1.png

Result:

2.png3.png

4.png

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. 

amitchandak
Super User
Super User

@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/367415

 

Color 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-values

 

edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

Hello @edhans

please see below what my table looks like. I only incuded the relevant columns.

 

ProductRevenueICD10
Drug A2D86.9
Drug A2T78.4
Drug B1R11

Drug B

1D86.9
Drug C

 

3R11

 

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:

edhans_0-1595880904443.png

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:

edhans_1-1595880980618.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.