Show multi-layer hierarchy in matrix

[MReinagl]

Hi,

I would like to visualise the multi-layer hierarchy of our articles master file.

What I have is a table showing all attributes for the articles and another one having the parent-child relation in two columns (i.e. one column showing the article no and another column showing the no of the parent article.

Obviously any article can occur in both of these columns.

I am able to show one level of the hierarchy in a matrix visual by having created a hierarchy in the parent-child table. But this shows only one level.

One approach I tried is to have a slicer which shows the choosen article with the matrix visual then showing its childs. Then I thought I can achieve what I want by clicking in the matrix and the clicked article is taken over to the slicer and the matrix would then show the children of the clicked article. But I cannot manage that.

Trying to give a simplified example: 

Let's assume this would be the articel master:

Article 1

|

+ Article 2

+ Article 3

  - Article 4

  - - Article 5

The main question is how to show this in Power BI.

The approach I described above would mean when Article 1 is choosen in the slicer Article 2 & Article 3 are shown in the matrix. And when clicking on Article 3, this one would be taken as slicer with Article 4 shown in the matrix and so on.

Any ideas would be much appreciated.

Thanks

M

[v-jingzhang]

Hi @MReinagl 

If I understand it correctly, you want to show only the next level children of an article when you select an article in the slicer. To achieve this, you can add an independent table which contains the unique IDs of all articles and use this ID column in a slicer to filter the matrix. 

Then create a measure to get which article is selected in the slicer.

Select Article = SELECTEDVALUE(ArticleIDs[Article ID])

And create a measure to decide whether the parent article of an article is the one you have selected in the slicer. If it is, return 1. Then put this measure in the visual-level filters of the matrix and set to show items when value is 1. 

Next Level Flag = IF(SELECTEDVALUE(ParentTable[Parent ID])=[Select Article],1,0)

You can download the attached .PBIX file for details.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it. 

[MReinagl]

Hi,

Thanks for your answer. Unfortunately this doesn't work for me. I believe because my hierarchy has no unique parent-child relationships, i.e. one element can have multiple parents.

However, I can achieve the same result as in your example by creating a hierarchy within the parent table. That's fine.

But what I want to be to now is a further drill-down step. So I thought maybe it is possible to click on a child-element and make this the selected element in the parent table.

In your exmaple: when I select 101 in the parent table, articles 102 and 103 show up in the child table. Now I want to click on 103 in the child table which then selects 103 in the parent table and shows 104 in the child table. Not sure how clear my explanation is to be honest.

thanks

Matthias

[v-jingzhang]

Additionally, if you have interest in the drill down features in a matrix visual, you can refer to this document: Create a matrix visual in Power BI. You can add multiple hierarchy level columns to your hierarchy table and use these columns in a matrix to drill down. Refer to this document for how to add hierarchy level columns: Parsing Organizational Hierarchy or Chart of Accounts in Power BI with Parent-child Functions in DAX

Regards,

Jing

[v-jingzhang]

Hi @MReinagl 

Currently in my example, if you select any article in the slicer, its child articles will show in the table if any. If you want to click an article in the table and want this selected value to filter the slicer to show this value only, I'm afraid I have no idea how to realize it. Sorry.

In addition, I found an alternative way to achieve yesterday's result. Create a relationship between 'ParentTable'[Parent ID] and 'ArticleIDs'[Article ID], then no need to use the [Next Level Flag] measure to filter the table. You can download the new attachment for details.

Regards,

Jing