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.
