Matrix table on already aggregated facts and lookups returns unexpected results

[plemo]

Hi All,

I'm currently running Power BI Desktop version:  2.105.923.0 64-bit (mai 2022).

Now trying to build a matrix table to let the users expand from the top level of a hierarchy and show all the related and already aggregated values, but can't get the desired result despite many different attempts. So I thought I'd ask the community!

Original data has this shape:

Level	Node Number	Parent Node Number	Node Name	Value
0	1	0	Top node	16
1	2	1	Node a2	7
1	3	1	Node a3	9
2	4	2	Node b1	7
2	5	3	Node b2	5
2	6	3	Node b3	4

What I'm after in a Power BI Matrix table:

Initial presentation:

Node	Value
+ Top Node	16

After expanding on the Top Node:

Node	Value
- Top Node	16
+ Node a2	7
+ Node a3	9

After expanding only on a2:

Node	Value
- Top Node	16
- Node a2	7
+ Node b1	7
+ Node a3	9

After expanding only on a3:

Node	Value
- Top Node	16
+ Node a2	7
- Node a3	9
+ Node b2	5
+ Node b3	4

Taking the initial table, in my database, I have created 4 views, one dimension (look up) per split level, 3 in the current example, and one fact containing the Node Number and the Value.

Each Dimension contains the Node Number, the Parent Node Number and the Node Name.

In Power BI Model, I have joined each dimension, cascading down from split level 0, from Node Number to Parent Node Number.

I have also joined each Dimension Node Number to the Fact Node Number.

I have removed all sums from all tables given that the values are already aggregated.

what I get is not the desired results as shown above when I stack the dimensions 0 and 1 in the right order from top to bottom as rows And Value in the Values section in the matrix Build Visual. I even get blank nodes and a grand total which is twice what it should be:

Obtained result:

Node	Value
-	16
Node a2	7
Node a3	9
- Top Node	16
	16
Total	32

Am I missing something in the model setup, such as making some of the fields nullable? Something else?

Or is this just not the way to do it? In which case, what is the best way to represent an already aggregated hierachical set of data?

Thanks for your answers!

Plemo

p.s. Sorry about the columns in the tables, I'm loosing them when posting

[lbendlin]

This is not the way to do it. Use the PATH functions in DAX and create a hierarchy from the PATHITEM() values.  You can do all this with a single table.

[plemo]

Thanks Ibendlin, I'm playing with the PATH and PathItem at the moment. It's looking better in terms of hierarchy. Using Path and PathItem I have created several columns from level 1 to level 10.

Though when I display it in the matrix, stacking level 1 to 10 as rows, I get an empty branch for each node I expand and I can't figure out why.

[lbendlin · Accepted solution]

Read about ragged hierarchies and how to handle them in Power BI.

[plemo]

Thanks so much for all the info you've given me, it's been really helpful!