4 different ways to dynamically change table’s rows with Power BI

Users often need to see the data in different angles to find the business insights they’re looking for. Instead of creating dozens of visuals in your reports, solutions exist to reduce the number of visuals while giving users control over it.

In this post, I’ll detail 4 different ways to achieve this, and I’ll discuss the pros and cons of each solution. The solutions also work if you want to dynamically change chart axis. In this example, I’ll  dynamically change table’s rows.

Business Specifications

I’ll take the Adventure Works SQL database for this example, and we’ll try to slice the FactResellerSales SalesAmount, based on 5 fields:

• EnglishProductSubcategoryName
• EnglishProductCategoryName
• SalesTerritoryGroup
• SalesTerritoryRegion
• SalesTerritoryCountry

As I’m writing this post (July 2019), there is no easy way to give users the ability to change a table’s rows from Power BI Services. I’ve seen that this feature is coming, as in this video,  we’re seeing a demo where they modify a visual on the fly, changing the axis of a chart.

However, this business specification can still be valid after the release of this feature, as users can be overwhelmed by the number of tables and fields available in the data model. In our business case, we want them to be able to select between 5 fields only.

Exploring different solutions

Rows Hierarchy

The easiest solution is to leverage the hierarchy feature on visuals. In our case, we just need to put our 5 columns on the rows, and explain users that rows are going to change when they click on “Go to the next level in the hierarchy”.

When clicking on it, here’s what happens. Rows changed from category name to subcategory name.

This solution isn’t ideal because in Power BI Services, users won’t be able to see the hierarchy, and if they want to split the table by country, they’ll have to click 4 times to the same button. The option is easy to implement but not user-friendly at all in an App.

Bookmarks

Bookmarks enhance a bit the previous solution, making it a bit more user-friendly. I won’t detail the steps here as it’s easy to implement. Here’s the result:

This solution has a major drawback: maintenance. Chances are if you start using this bookmark trick, you’ll end up having bookmarks all over the place, and it can be a nightmare to maintain.

Plus, bookmarks take some space in a report, and they’ll become a problem if you want users to be able to choose between 20 columns rather than 5. Overall, the solution with bookmark is a good patch, but isn’t a scalable and maintainable solution.

The next 2 solutions are more scalable, as the users will be able to pick the column name from a slicer. Slicer don’t eat much space in the report, and you can search values in it. Hence, the next solutions are way more scalable.

Unpivoting a table

If we wanted to change the table’s rows from columns available in a single table – for example, if we only wanted these 3 columns from the DimSalesTerritory table: SalesTerritoryGroup, SalesTerritoryRegion and SalesTerritoryCountry – another solution worth mentioning is to create a new table by unpivoting all columns we want to use in our slicer.

To achieve this, I duplicated the DimSalesTerritory table and renamed the new one as DimSalesTerritoryUnpivoted. I selected the 3 columns, and clicked on Unpivot columns. Then I kept 3 columns, which ends up by having the following table:

And here’s how this table is linked to the data model.

I changed the cross-filter direction from single to both, because we’ll use the Attribute column from the new table to filter the DimSalesTerritory table. That’s right, the attribute column is our slicer, and in our visual, the value column from the new table is our dynamic rows.

In the end, users have the flexibility to select a column in the slicer, and the matrix table is updated based on the user selection.

Even though this solution works fine with one table, it doesn’t work in our example, where we need 5 columns from 3 distinct tables. The next solution, similar to this one, solves this issue.

DAX Table and Measure

With this last solution, we’ll do something very similar to what’s above, but we’ll make it work with columns from different tables.

The first step is to create a table with all columns name in a column, and all corresponding values in another column. We don’t need any keys, as we won’t link this table to the model. We’ll create later a DynamicSumSalesAmount measure to leverage this new table.

We could create this table by unpivoting all 5 columns, and appending the 3 queries (CategoryUnpivoted, SubCategoryUnpivoted and SalesTerritoryUnpivoted). However, that would create 3 unnecessary tables, and if we wanted later to add a few columns, that would be painful. Rather, we can create a DAX table to achieve the same thing.

It gives us something like this:

Now we need to create a measure on the FactResellerSales table to leverage this DAX table. The trick is to use the TREATAS DAX function, which “Treats the columns of the input table as columns from other tables. For each column, filters out any values that are not present in its respective output column.”

On the matrix table, I put the slicer table values in rows, and the SumSalesAmount (Dynamic Slicer) in values. The Slicer table type should be placed as a slicer. In the end, the result is fast, user friendly, and doesn’t change the data model drastically.

In an enterprise data model, you could use this DAX table with dozens of columns, and use it to dynamically filter visual in any report. You just need to remember to update the measure that use this table whenever you add or remove a column in the table. To use this solution with many reports, the trick is to add a visual level filter to select columns you want your users to be able to change the visual with.

Your model won’t be any bigger with this solution as long as you don’t add any column that have many different values (like customer name or date for example). In this example, my slicer table has 65 rows.

File

In this file below, you’ll find the four different solutions put together into a PBIX file.