Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
amitnarkar3
Helper I
Helper I

create hierarchy filter based to work with flattened hierarchy table

Hi Team,

 

I have a a table with flattened hierarchy with data as Location, Sublocation, Asset where Sublocation row gives aggregated data for underlying assets. However now I need to use Hierarchy filter to work with this flattened hierarchy data table.

 

My objective is to use precalculated rows as it based on slicer selection. My last option is to create a hierarchy table based on parent child relation tables which I have and join it with my flattened data table on lowest granularity i.e. Asset and then power bi let take care of any roll ups calculations based on slicer selection. But it raises a question of performance.

 

I am trying to find out better alternative to do this. I found an article regarding creating hierarchy.

http://www.daxpatterns.com/parent-child-hierarchies/

 

But as per article I could not use path function as in my data set child has multiple parent nodes. Apart from this issue, there is a possibility of asset directly rolling upto Location which kinds of create ragged hierarchy.

At this moment I am not sure how do I model hierarchy from my 3 tables (asset, location and sublocation with parent child relationship) with a possibility of ragged hierarchy and use it along with my flattened table.

 

I also tried creating 3 different views on my flattened data table and joined it respectively to different hiearachy level ids. This works as far as I dont have ragged hierarchy. I believe ragged hierarchy is not supported by power bi. I would appreciate any ideas regarding how do I create hierarchy table that I could consume with flattened table. I am really confused at this moment.

 

 

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @amitnarkar3,

 

>>But as per article I could not use path function as in my data set child has multiple parent nodes. 

Since your relationship is one to many, I'd like to suggest you use the column which in "one" side to create the hierarchy column, then drag the "many" part to it.

After above steps, drag the new hierarchy column to hierarchy slicer as the source.


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Amratya
Helper I
Helper I

Have you tried the HierarchySlicer Custom Visual?

you can find it here

https://app.powerbi.com/visuals/show/HierarchySlicer1458836712039

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.