How to change the tables rows dynamically in Power BI !!!

Making Power BI more powerful using dynamic measures: In this post I will show you how you can use a slicer and filter the tables on multiple dimensions:

Business Problem: Suppose you have multiple dimensions e.g. Country, Region, Supplier, Customer etc. and you want to see the sales by year by these dimensions

This week got to be an exciting one for me where I had to perform some interesting operations in power BI. It's always better to create dynamic reports in whatever platform you use to be able to reuse the features again next time. In Power BI you would have to write some DAX measures for this. For such use this time I got to learn and implement some dynamic features in power bi which i will show you now.


You can install and configure the Adventure Works SQL database and practice or you can try to implement it on your own data.

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15

Solution 1: Using the Hierarchy feature

We can leverage the hierarchy feature on visuals. In this case, we just need to put our 5 columns on the rows, and rows will change when one clicks on “Go to the next level in the hierarchy”.

But the problem is that 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.

Solution 2: Un-Pivoting a table

To achieve this, make a copy of DimSalesTerritory table and rename to DimSalesTerritoryUnpivoted select the 3 columns in DimSalesTerritory table and on Unpivot columns. Then keep the 3 columns, which ends up by having the following table:

The table will look like this

                                                                

Link the table to the data model:

Change the cross-filter direction from single to both, because we’ll use the Attribute column from the new table to filter the DimSalesTerritory table. 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.

DAX table and measure:

The first step is to create a table with all columns name in a column, and all corresponding values in another column. We will leverage this later.

To make it dynamic, we would create a DAX table to achieve the same thing.

                                                    

                                                       

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.

While implementing it on your own data, 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.

Hope this is helpful!