Dynamic Column Sorting and Switching Columns and Rows with Field Parameters

Continuing on my exploration of field parameters, in this post I want to share how you can use field parameters and modelling to dynamically sort columns in a table with a slicer and also switch rows and columns in a table. The technique can also be used to change the sort on a hierarchy but with some limitations. You can view my previous post on field parameters with data modelling and calculation groups here: https://tenfingerseddy.wordpress.com/2022/05/19/exploring-field-parameters-in-power-bi/

To set this up we create three versions of the same field parameter. The three versions are identical except they are each sorted differently and named accordingly. I’m using Gender, Education and Occupation columns for this example so have named the tables with initials indicating the sort order in each field parameter.

We then create a ‘Hierarchy Sort” table to sort with as follows:

Relationships are created between this table and the three parameter fields using the Key to the Order column in the field parameters.

As you can probably now see, filtering the Hierarchy Order column in our Hierarchy Sort table to “Education, Gender, Occupation” returns 0 in the key column which filters the order columns in the three field parameters to 0 resulting in the three different columns across them.

We add ‘Hierarchy Sort'[Hierarchy Order] to a report as a slicer and then add all three field parameters to a table.

We can now cycle through the different column ordering in the table using the slicer. The order of the field parameters is static but the column each returns changes.

The real reason I looked into this is that I have always wanted a way to make a dynamic hierarchy in a visual and give users the ability to choose the order in which they drill categories kind of like a decomposition tree but in a matrix or chart but without using bookmarks to switch visuals. This technique can be used to switch a hierarchy of columns around but the end result is a bit frustrating.

The switching works, you do get the ability to choose different hierarchies but there is a big catch. If I am looking at the hierarchy Occupation, Education, Gender

Then I switch to Gender, Occupation, Education:

The visuals hierarchy will stay on Occupation so you are switched to level two of the new hierarchy selected. You also land in the new hierarchy level not expanded from a previous level and with no context from other levels of the hierarchy. This means you need to navigate through the hierarchy again to fully expand all columns (I was hoping to be able to switch in a fully expanded view). So it kind of works if you know how to use drill controls but the user experience isn’t great. Bookmarks are still a better option for now. You can actually rearrange a hierarchy just by using a field parameter with multi select and the hierarchy will respect the the order you select in ( Thanks to Mustafa Aşıroğlu for pointing this out to me on twitter) but it would be nice to be able to choose a specific order with one click.

There’s also some interesting effects you can achieve in other visuals using the same concept. A simple one is switching the columns and rows in a matrix.

Here’s is an example where I have loaded the previously created field parameters into a clustered column chart with small multiples. The field parameters are used in the x axis, legend and small multiple wells:

This allows a user to cycle through different ways of visualising the same fields. I think its fairly powerful, if we want to explore a particular metric by a few different fields, it allows users to explore them more deeply by switching up how they are visualised using a single slicer. In the screenshots Gender is switched to the legend, Occupation to the X axis and Education becomes the small multiple category.

They are a lot of fun these new field parameters! 🙂

Update: 02/06/2022 – Turns out there’s a fairly simple way to switch the sort order of a hierarchy with field parameters. Check it out in Sam Fischers post here: https://apexinsights.net/blog/swapping-gantt-charts-with-field-parameters