Field Parameter - Hierarchy

[joshua1990]

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?

[mostvp123]

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:

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

 

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

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:

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.

[alya1]

Thank you so much for your solution! I created 3 layers of parameter tables for Year, Month, and Date. But for some reason I couldn't do 1 to 1 relationship and cross filter Both. I had to do Many to 1 (lower level to higher level) and cross filter Single. It still looks to be working. Do you know if this is ok? 

[mostvp123]

Glad it helped!!

One to many should work fine, as long as your hierarchy drills down in the one -> many direction. I'm not sure why you can't set up one to one though (note that one to one is always cross-filter both). Make sure that each of your rows in your relationship columns have exactly one corresponding row with the same value in both tables. A screenshot of your field parameter tables would help! 🙂

[alya1]

That's great, thank you! Yes, I am quite perplexed as well. Please see attached screenshots, thank you for taking a look: 

[mostvp123]

I tried to replicate but couldn't. I wonder if this has to do with the usage of Auto Date/Time in your model (I always switch that off and use a date table instead). Some other causes could be additional relationships somewhere between these tables which are causing the issue. I'd suggest adding these 3 tables only to your relationship view, and then right click on each table -> "Add related tables". This will allow you to check if you have any "extra" relationships with other tables in the model, which need to be deleted before being able to create the one-to-one relationship. That said, if your output is workign as intended - I wouldn't worry too much about it 🙂 The only difference is you won't be able to invert your hierarchy if you want to use the drill down buttons in your visual.

[alya1]

makes sense, thank you again! 

[otravers]

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

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/

[james_batchelor]

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]

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..?