cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
cuongle
Advocate II
Advocate II

Self Joined tables filtering

Assume I have table `Category like below`:

 

Capture.PNG

 

This table has self relationship between Id and ParentId, Level column is used to indicate category level. My real problem will have 3 level of category. In here, I just give simple assumption.

 

I got a little bit stumped how to visualize category based on level. I want to have 2 charts (pie chart or column chart) to visualize category level 1 and level 2 based on Value from other Columns (ex: Salemount from Sales table).

 

And when users click Category L1 chart, Category L2 is filtered accordingly. How I can do this on self joined table. 

1 ACCEPTED SOLUTION
Meagan
Advocate V
Advocate V

You can flatten your hierarchy out in your Power BI model using calculated columns. If you have 3 levels, add 3 columns. If you think the length of the hierarchy might grow (e.g. you might have 4 levels in the future), go ahead and add an extra column. 

path functions in DAXpath functions in DAX

I just call my columns Level1, Level2, Level3. I also added a Fake Sales value at the end of my table so I would have something to visualize, but you can ignore it. 

 

Here are the calculated column formulas: 

Level1 = LOOKUPVALUE(Sheet1[Name],Sheet1[ID], PATHITEM(PATH(Sheet1[ID], Sheet1[ParentID]), 1, 1))

Level2 = LOOKUPVALUE(Sheet1[Name],Sheet1[ID], PATHITEM(PATH(Sheet1[ID], Sheet1[ParentID]), 2, 1))

Level3 = LOOKUPVALUE(Sheet1[Name],Sheet1[ID], PATHITEM(PATH(Sheet1[ID], Sheet1[ParentID]), 3, 1))

 

You'll notice the formulas are remarkably similar, with only one number changing, which corresponds to the level. 

 

To break this down, the PATH() function traverses the hierarchy based upon the ID and Parent ID. PATHITEM() retrieves a specific item in the path. LOOKUPVALUE() retrieves the name associated with the ID that was returned by PATHITEM. So you are essentially saying: for the ID in this row, give me the Name at level X in the hierarchy. The hierarchy works such that level 1 is the top parent (computer or game). Since your example contains only 2 levels, values for Level3 are blank.  

 

Then you can visualize your data. I have example bar charts:

PBIHierarchyHIghlight.pngBy default, when you select a Level1 value in the left chart, you will see the related Level2 values highlighted in the right chart. If you would prefer to filter instead of highlight, you need to Edit Interactions and change the Level2 chart to filter instead of highlight. 

PBIHierarchyFilter.png

 

View solution in original post

3 REPLIES 3

Hi @cuongle,

 

I can only agree with @greggyb. Doing the transformation before importing the data into the data model is also my prefered way of building data models. The way @Meagan explained, is the way the Italians (Marco and Alberto) explain how to solve the problem of flattening a parent child hierarchie into a table with one column per hierarchy level. And of cause this is a good solution. But as both explain in their book, there are some obstacles and I think the haviest is, that you have to know how "deep" your hierarchy is and will be in the future. The deeper your hierarchy, the more columns you need to prepare in your table with DAX.

 

@greggyb mentioned the merge function in Power Query. This for sure is a good solution... But I once discovered an article by Chris Webb, dealing with following topic: Flattening A Parent/Child Relationship In Data Explorer (Power Query)

 

Even, if there are no loops in Power Query, the M language has the ability to write recursive function... functions, that call the function itself. It took me a while to get behind that concepts, but it is really worth reading and understanding this excellent article.

 

Hope that helps and gives some new ideas.

 

Regards,

Lars

Meagan
Advocate V
Advocate V

You can flatten your hierarchy out in your Power BI model using calculated columns. If you have 3 levels, add 3 columns. If you think the length of the hierarchy might grow (e.g. you might have 4 levels in the future), go ahead and add an extra column. 

path functions in DAXpath functions in DAX

I just call my columns Level1, Level2, Level3. I also added a Fake Sales value at the end of my table so I would have something to visualize, but you can ignore it. 

 

Here are the calculated column formulas: 

Level1 = LOOKUPVALUE(Sheet1[Name],Sheet1[ID], PATHITEM(PATH(Sheet1[ID], Sheet1[ParentID]), 1, 1))

Level2 = LOOKUPVALUE(Sheet1[Name],Sheet1[ID], PATHITEM(PATH(Sheet1[ID], Sheet1[ParentID]), 2, 1))

Level3 = LOOKUPVALUE(Sheet1[Name],Sheet1[ID], PATHITEM(PATH(Sheet1[ID], Sheet1[ParentID]), 3, 1))

 

You'll notice the formulas are remarkably similar, with only one number changing, which corresponds to the level. 

 

To break this down, the PATH() function traverses the hierarchy based upon the ID and Parent ID. PATHITEM() retrieves a specific item in the path. LOOKUPVALUE() retrieves the name associated with the ID that was returned by PATHITEM. So you are essentially saying: for the ID in this row, give me the Name at level X in the hierarchy. The hierarchy works such that level 1 is the top parent (computer or game). Since your example contains only 2 levels, values for Level3 are blank.  

 

Then you can visualize your data. I have example bar charts:

PBIHierarchyHIghlight.pngBy default, when you select a Level1 value in the left chart, you will see the related Level2 values highlighted in the right chart. If you would prefer to filter instead of highlight, you need to Edit Interactions and change the Level2 chart to filter instead of highlight. 

PBIHierarchyFilter.png

 

greggyb
Resident Rockstar
Resident Rockstar

Another option is using Power Query's native join functionality, called Merge, to do this flattening. You can merge a query with itself, e.g. on ParentID - ID

 

When performing transformations and adding columns pre-Data Model, the storage engine can achieve superior compression. With the relatively small data limits enforced for hosted models in the Power BI Service, I always lean toward performing my transformations before importing the data into the model, and using DAX solely for data access and measure definitions.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors