cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Clara Member
Member

How to work with hierarchical data?

Hello everyone! I'm full of questions today.

 

I have a table (actually dozens of similar tables, which is why I need Power Query in the first place) with data which looks like this:

 

1SALES730000
1.1     FOOD500000
1.1.1          Pizza200000
1.1.2          Cheeseburger300000
1.2     DRINKS150000
1.2.1          Soda100000
1.2.2          Juice50000
1.2.3          Water

80000

2EXPENSES

170000

2.1     Flour50000
2.2     Meat100000
2.3     Vegetables20000
3INVESTMENTS200000

 

After transforming this table in the Query Editor, I came up with something like this:

 

1nullnullSALESnullnull730000
11nullSALESFOODnull500000
111SALESFOODPizza200000
112SALESFOODCheeseburger300000
12nullSALESDRINKSnull150000
121SALESDRINKSSoda100000
122SALESDRINKSJuice50000
123SALESDRINKSWater80000
2nullnullEXPENSESnullnull170000
21nullEXPENSESFlournull50000
22nullEXPENSESMeatnull100000
23nullEXPENSESVegetablesnull20000
3nullnullINVESTMENTSnullnull200000

 

I want to be able to analyse this data in a way that doesn't duplicate/aggregate values when it shouldn't. I can't simply filter out the 'nulls' because as you can see, in some cases my items do not branch out into smaller categories.

 

What should I be doing differently?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: How to work with hierarchical data?

@Clara,

I create the following measures in the table. Then I set the value of Measure to 0 in visual level filter. If the DAX don't return your expected result, please post your desired result here.

chk1 = var maxvalue= CALCULATE(MAX(Table2[value]),ALLEXCEPT(Table2,Table2[level1dec])) return IF(maxvalue=MAX(Table2[value]),1,0)
chk2 = CALCULATE(COUNTA(Table2[level1dec]),ALLEXCEPT(Table2,Table2[level1dec]))
Measure = IF([chk1]=1 && [chk2]>1,1,0)

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Clara Member
Member

Re: How to work with hierarchical data?

Okay! Here's what I came up with:

 

chk3 = VAR level2 = SUMX('Table1','Table1'[level2])
RETURN IF(ISBLANK(level2),1,0)
Measure = IF([chk1]=1 && [chk2]>1 && [chk3]=1,1,0)

This is possibly not the most efficient solution, but it worked in my case 🙂 Please let me know if there is a smarter way to do it!

View solution in original post

4 REPLIES 4
Moderator v-yuezhe-msft
Moderator

Re: How to work with hierarchical data?

@Clara,

I create the following measures in the table. Then I set the value of Measure to 0 in visual level filter. If the DAX don't return your expected result, please post your desired result here.

chk1 = var maxvalue= CALCULATE(MAX(Table2[value]),ALLEXCEPT(Table2,Table2[level1dec])) return IF(maxvalue=MAX(Table2[value]),1,0)
chk2 = CALCULATE(COUNTA(Table2[level1dec]),ALLEXCEPT(Table2,Table2[level1dec]))
Measure = IF([chk1]=1 && [chk2]>1,1,0)

1.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Clara Member
Member

Re: How to work with hierarchical data?

Thanks Lydia! I did some extra tweaking since I also needed to filter out some of the 'level2' values but your help was absolutely crucial!

 

Here's what I ended up with (exaclty as I needed (still kind of a mess but yeah)):

 

ndsl.PNG

Clara Member
Member

Re: How to work with hierarchical data?

@v-yuezhe-msft just a follow-up question. Let's say this happens:

 

1SALES730000
1.1     FOOD500000
1.1.1          Pizza500000
1.1.2          Cheeseburger0

 

From what I understood, chk1 "discards" the row if its value matches the maximum value for the level. In this case, it would discard 1.1 FOOD (correct) but also 1.1.1 Pizza (incorrect), leaving me only with (nonexistent) Cheeseburger sales. What would be the most efficient workaround in this case?

Clara Member
Member

Re: How to work with hierarchical data?

Okay! Here's what I came up with:

 

chk3 = VAR level2 = SUMX('Table1','Table1'[level2])
RETURN IF(ISBLANK(level2),1,0)
Measure = IF([chk1]=1 && [chk2]>1 && [chk3]=1,1,0)

This is possibly not the most efficient solution, but it worked in my case 🙂 Please let me know if there is a smarter way to do it!

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 87 members 1,268 guests
Please welcome our newest community members: