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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
joshua1990
Post Prodigy
Post Prodigy

Field Parameter - Hierarchy

Hi community!

Using field parameters we can switch easily between dimensions.

Is there any chance to switch between hierarchies with field parameters?

Somehow like:

  • 1. Country
    • City
  • 2. Department
    • Team

Is this somehow possible in Power BI without Bookmarks?

4 REPLIES 4
mostvp123
Advocate V
Advocate V

Hi @joshua1990 I had the same issue and I found something that worked!!

I set up two separate field parameter tables which have a one to one relationship with each other on an additional column (Value4 in my model) in the “top layer” field parameter table. You just need to edit the top layer field parameter table DAX slightly. I made a simple data model with a master data table and 3 dimensional tables with 2 layers of hierarchy (Region -> Country, Vertical -> Industry, Product Group -> Detailed Product)
Here is the result...

Top level:

mostvp123_0-1665501592187.png

Ability to drill down (expanded Hierarchy, but all the other drill down options work):

mostvp123_1-1665501592191.png

 

mostvp123_2-1665501592193.png

Sample model (Requires additional “Value 4” column):

mostvp123_3-1665501592198.png

Table definition for "Parameter" table - note I generated this table with the Field Parameter feature, then edited the definition slightly to add the second hierarchy layer name:

mostvp123_4-1665501714458.png

Then the parameter fields from each parameter table go into your visual. 

I’ve attached the sample pbix: Field Parameter within Field Parameter.pbix Let me know if this works for you!
14/06/23 - Link has now been updated, seems it has expired, should be working again.

otravers
Community Champion
Community Champion

Hierarchies are not natively supported by field parameters, as you can see in this screenshot:

otravers_0-1660428503835.png

 

However I've been able to make them behave like a hierarchy for the purpose of setting up axes in visuals, like the x-axis in this blog post where I do year>quarter>month via field parameters:

https://www.oliviertravers.com/data-driven-power-bi-formatting-field-parameters/

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

I came here looking to do the same thing.   I have a matrix table where I want to allow users to change what dimension (hierarchy) is on the rows.  ie Geo hierarchy  or Trade Class hierarchy  or Product Hierarchy.   I see hierarchies are greyed out as selectable items when building field parameters.  any tricks here?   I really do not want to turn on full personalization for the whole report.   

 

thx

james

mohammedadnant
Impactful Individual
Impactful Individual

Hi @joshua1990 

 

You mean you want to select Country or Department, it has to show Country--> City or Department-->Team..?

Can you please share your file..?

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

Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors