Dynamic Rows and Dynamic Columns in Power BI Matrix visual using Slicers

Power BI is one of the most popular tools in data analytics and many businesses like to see their data in tabular formats. Power BI is equipped with ‘Table’ and ‘Matrix’ visuals to achieve this but when it comes to show rows and columns dynamically by empowering users to choose rows and columns they want to see, Matrix visual is quite useful.

To illustrate it, we have following dataset. If you notice, we have dimensions like Product ID, Category, Sub-Category and Product Name and values like Sales, Quantity, Discount, and Profit.

Say, we want to to see Total of Sales, Total of Profit and Average of Quantity based on dimensions : Then? At first, we should create a static table using “Enter data” option in Power BI desktop:

Now, we need to write following DAX with Switch case:

Next, we need to follow following path in Power BI desktop:

File >> Options and settings >> Options >> Preview Features >> Field Parameters

Once we check Field parameters option, we will see below pop-up:

Now, if we notice the parameters option before restarting Power BI desktop, it will look like this:

But, as soon as we restart Power BI desktop, we see below option of Field available:

Now, we can search the columns we want to make dynamic and click on Create:

It will create an automatic table like below:

Now, add Dynamic Columns as a slicer:

Then, add Column name from Static Table (created earlier) as another slicer:

Now, we are ready to drag Matrix visual in our Power BI desktop canvas. Initially, it will look like this with “Field Parameters” in Rows, Calculated value names in Columns (from Static table) and Switch case DAX in values.

As we have two more rows and currently we can see only Category row, if we click on drilldown icon (where 4 is written in screenshot) two times, we will see like below:

If we need to change the look, we can un-select following option:

Now, if we select any columns from slicer, we will see only those in matrix visual (as shown below):

Note: There is one limitation. Every time we select ‘Dynamic Columns’ from slicer, we must click on drill down icon (in green box) to see all the columns.

I have shared my own experience in this article. Please share your thoughts if you find anything incorrect here. You can also search this article by using strings like Dynamic rows, columns and values in matrix.

Twitter: @MrTomarOfficial

LinkedIn: https://www.linkedin.com/in/raghvendra-pratap-singh-tomar/