Forum Discussion
Self Joined tables filtering
- 10 years ago
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 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:
By 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.
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 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:
By 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.
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.