The might of field parameters lies in the parameter slicer and the flexible behavior it demonstrates as a user plays with the slicer.

Field parameters work great to change dimensions or create hierarchies with a multi-select slicer. Whereas, using a single select slicer, things don’t work out if you try to build a dynamic matrix (or any other visual with hierarchies).

In this article, we look at an innovative implementation of field parameters to dynamically change hierarchical and non-hierarchical dimensions in a matrix using a single select slicer.

---

Whether developing a new report or updating existing reports, leveraging field parameters is relatively straightforward. In a previous article, we talked about the field parameters and all the amazing things they could do to simplify the life of a Power BI developer and the end-users.

Making most of Field Parameters in Power BI

We leveraged field parameters to reduce the number of bookmarks in a reporting solution to simplify the maintenance and reduce the development efforts. Everything worked fine until we ran into a scenario where the field parameters didn’t work, which is this article’s topic.

Report Setup

Our report has multiple pages with buttons and bookmarks to implement the dynamic dimensions. The dimension change applies to tables, visuals, and matrices. Buttons are used to navigate the bookmarks to show the visual for the selected dimension. Effectively, we have one visual per dimension that becomes visible per the user’s selection. The following image imitates our report setup.

We have a table for the Category dimension, whereas we have a matrix for the Regions dimension. The matrix shows a hierarchy of groups and regions in rows.

The tricky situation

Field parameters worked great to simplify our dynamic dimension approach for tables and other non-hierarchical visuals. However, the matrices posed a challenge in our scenario.

You can use field parameters to build a dynamic hierarchical matrix given that the user selects multiple values in the parameter slicer. For example, we created a field parameter with three fields: categories, groups, and regions.

If we select both Group and Region, the matrix works correctly and gives the same results as our bookmark approach. Choosing just the Category works fine, too. A matrix behaves like a table if hierarchies are not in the play.

However, this approach fails if we want to show hierarchy with a single selection in the slicer. This is because parameters don’t support hierarchies as fields, and you can’t supply more than one field in the parameter DAX either.

So how do we replace our dynamic dimension approach with the field parameters? For Category, we need a table; for Region, we need a matrix with group and region hierarchy. Selecting categories works fine, but selecting regions gives us just another table without the hierarchies.

And of course, we don’t want to show three options in the slicer and ask our users to select multiple options in the slicer.

And the crafty solution

The following image illustrates the solution to our problem. A parameter slicer with two options: Category and Region. The region displays the group and region hierarchy in rows, and the Category displays a table.

How did we do it?

The short answer: We used two different field parameters to arrive at the solution. The first parameter contains the slicer values Category and Region and their respective fields. The second parameter contains the field for the Group used to create the hierarchy in the matrix. This second parameter is NOT shown on the page as a slicer. We edited the DAX for both the parameters and added a new column to create a relationship between the two tables. Then we used the parameter dimensions from both the parameter tables in the matrix.

The slicer from the first parameter table filters the second parameter table utilizing the relationship, and we get our desired matrix for regions and a table for categories. This approach takes advantage of DAX concepts like expanded tables and filter contexts to achieve the final result. Got it?

Now, the long answer. Let’s look at the steps:

1. Create a field parameter with the fields that will be visible as slicers in the report, i.e., Category and Region, and name it “Parameter Dimensions”. Then, select the option to add the slicer to the page.

2. Create another field parameter with the fields we need to show in the matrix, i.e., Group, and name it “Matrix Dimensions”. We don’t want to add it as a slicer on the page.

3. Edit the DAX of both the parameters to add a fourth column. This column should have the same value for the fields that will be used to create hierarchies. The goal is to create a relationship between these parameter tables in the following steps. For our problem, we added a new column with two values: ‘Y’ for Region and Group and ‘N’ for Category. You can choose any set of values you see fit for your scenario.

4. Rename this column to bring clarity to its purpose and hide it from the report view. We renamed this column to ‘Hierarchy Flag’ in both the parameter tables.

5. Create a relationship between the Parameter Dimensions and the Matrix Dimensions table using the new column. Your relationship cardinality and the cross filter direction might differ based on how many fields you have present in your parameters. The approach will work as long as the Parameter Dimensions table can filter the Matrix Dimensions table.

6. Add the parameter tables’ dimensions to the matrix’s Rows field drop zone. Adjust the order of the fields to get your desired view.

And voila! You have a dynamic matrix with dynamic dimensions that shows hierarchies dynamically with a single select slicer.

Conclusion

Field parameters are beneficial to reducing the development and maintenance efforts of the developers and provide more flexibility to users. The things work great for simple non-hierarchical dimensions, whereas it gets tricky to work with a mix of hierarchical and non-hierarchical dimensions.

We looked at an innovative approach to address this challenge by leveraging well-established DAX concepts like expanded tables and filter contexts.