How to work with hierarchical data?

[Clara]

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:

1	SALES	730000
1.1	FOOD	500000
1.1.1	Pizza	200000
1.1.2	Cheeseburger	300000
1.2	DRINKS	150000
1.2.1	Soda	100000
1.2.2	Juice	50000
1.2.3	Water	80000
2	EXPENSES	170000
2.1	Flour	50000
2.2	Meat	100000
2.3	Vegetables	20000
3	INVESTMENTS	200000

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

1	null	null	SALES	null	null	730000
1	1	null	SALES	FOOD	null	500000
1	1	1	SALES	FOOD	Pizza	200000
1	1	2	SALES	FOOD	Cheeseburger	300000
1	2	null	SALES	DRINKS	null	150000
1	2	1	SALES	DRINKS	Soda	100000
1	2	2	SALES	DRINKS	Juice	50000
1	2	3	SALES	DRINKS	Water	80000
2	null	null	EXPENSES	null	null	170000
2	1	null	EXPENSES	Flour	null	50000
2	2	null	EXPENSES	Meat	null	100000
2	3	null	EXPENSES	Vegetables	null	20000
3	null	null	INVESTMENTS	null	null	200000

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?

[v-yuezhe-msft · Accepted solution]

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

Regards,
Lydia

[v-yuezhe-msft · Accepted solution]

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

Regards,
Lydia

[Clara]

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

1	SALES	730000
1.1	FOOD	500000
1.1.1	Pizza	500000
1.1.2	Cheeseburger	0

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 · Accepted solution]

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!

[Clara]

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)):