cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Dynamic Hierarchy

Hello Friends,

I have a requirement in one of my reports. Trying to convert from SSRS to the awesome Power BI reports.

I have a report which has different category 
1. Products
2. Country
3. Date

Is it possible to create a dynamic Hierarchy for these 3 categories.? i.e the user should be able to select each of the above three categories in no specific order and the resultset ( which is in a matrix visualization) should change accordingly. The resultset has sales data in case you guys were wondering on the summarized column. Sales fields are Gross Amt, Net Sales, Import Sales, Export Sales etc.

I tried to use bookmarks but apparently it does not change the level of the fields.

 

In traditional SSRS setup, we have to one report for each of the combination of hierarchy 

Product =>

  • (Countries containing this product)
    • Date ranges of these country product combination

Let me know if you need further clarification.

 

Abhi

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @agurupra,

 

Sorry for the delay in seeing this but issues at work. 

 

Althoiugh I have seen some links to make the hierarchy slicer change they are based on values with the same table:

 

https://xxlbi.com/blog/dynamic-hierarchies-in-power-bi/

 

Believe that your options is to create 3 matrix visuals one with each hierarchy level order, then create a bookmar for each one and hiding the other two tables then just add buttons and link them to the bookmarks.

 

Check example attach.

 

hierarchy.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Hi @agurupra,

 

Can you please explain better waht you want to achieve? What do you mean by:

 

" i.e the user should be able to select each of the above three categories in no specific order and the resultset ( which is in a matrix visualization) should change accordingly."

 

The use of bookmarks can change the level of the fields but you need to setup everything in the correct way, on your explanation I'm not abble to understand your needs.

 

 

 

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler)

 

Regards,

MFelix
 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted

Selection Hierarchy 2Selection Hierarchy 2Selection Hierarchy 3Selection Hierarchy 3Selection Hierarchy 1Selection Hierarchy 1

Hello @MFelix

 

Attached is my exact requirement. I have created 3 sample pages: with different levels of Hierarchy / selection. I want to use bookmarks or something similiar in Power BI to give users the option to select which hierarchy they want to view the reports. I do not want to go in the "creating 3 report pages" route. Below is the link from where I downloaded sample data for Power BI ( for your reference).

 

Let me know if I was clear.

Abhi

 

Financial Sample for Power BI

Highlighted

Hi @agurupra,

I have been travelling work related.

I didn't forgot about this. I will answer soon sorry for the delay.

Regards,
MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted

Hi @MFelix

 

Appreciate the update. 

Highlighted

@MFelix any help on this is much appreciated ?

 

 

Highlighted

Hi @agurupra,

 

Sorry for the delay in seeing this but issues at work. 

 

Althoiugh I have seen some links to make the hierarchy slicer change they are based on values with the same table:

 

https://xxlbi.com/blog/dynamic-hierarchies-in-power-bi/

 

Believe that your options is to create 3 matrix visuals one with each hierarchy level order, then create a bookmar for each one and hiding the other two tables then just add buttons and link them to the bookmarks.

 

Check example attach.

 

hierarchy.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted

@MFelix

 

That is just the exact solutions I was looking for. Thanks a ton. You are a true datanaut.

 

I will definitely consume this . 

Highlighted

Hi guys. I know I'm 2 years late to the party however . . I found this YouTube video by Parker from Bi Elite. The reason for mentioning it here is that this community post is always found in the top spot in Google's search results whenever someone searches for 'Dynamic hierarchies in Power BI'. I recently faced the obstacle @agurupra did. So I followed the process mentioned in the video. Although there's a different kind of problem I'm facing . .

 

The functional hierarchy I have in my dataset has 3 levels. Now, ideally, I should only inlcude 2 chiclet slicers (I assume you have watched the video, it's quite informative) & Power BI should automatically show rows from 3rd level of hierarchy when I expand down to the last level. I does do that except when I have expanded ALL the levels, in the rows for last level (within the matrix visual), I get repeated data from most recent levels. How do I get rid off of those extra rows ? I'd also like to mention that if I insert another slicer for the 3rd (implied) level in hierarchy (whatever that may be), then the redundant rows do not appear.

 

P.S. : Apologies in advance if this sounds confusing but this is my first post in Power BI Community 😅 & I cannot post images or any sort of data due to confidentiality issue. ¯\_(ツ)_/¯

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors