Making your Power BI report dynamic can be both gratifying and painful. Not many things compare to adding a few simple buttons that update all your charts, providing new layers of information that would otherwise be forced into new visuals that clutter your report. However, this process isn’t always easy, especially when you want to change between measures. Some chart elements, like fill colours and rank measures, can be unresponsive to changes without specific workarounds. This blog will focus on updating rankings within a dimension based on a selected measure. As a bonus, I’ll show you how to incorporate this into a Top N filter, narrowing the scope of your chart to the most important elements.
The Problem
The bar chart below shows a list of boating accident causes in the United States, as ordered by the selected measure. Currently, the chart updates just fine, but we don’t want to show hundreds of accident causes. Ideally, we would like our user to select how many of the top accident causes will be shown.
Basic Set Up
This blog will assume you have the following set up in your Power BI:
- A measures table with your measures of interest (these will be used in the tile slicer to switch between measures).
- A measures parameter with these same measures as fields.
- A chart using the measures parameter.
- A tile slicer for the measures parameter (test to make sure this already affects your chart)
Note: If you don’t have this set up, you can follow along with this example (Download Power BI Follow Along File) or use the above links to help set up your own Power BI Report.
Step 1 – Add a Dummy Column to Your Measures Parameter Table
Click the Table View icon from the options on the left-hand side of the screen.
From here, select the Measures Parameter table in the Data pane, then click New Column from the Ribbon.
In the open Dax Formula Bar, write an expression to duplicate the measure names from the column Measures Parameter into a “Dummy” column. You could call this column anything but try keep your naming conventions intuitive.
Step 2 – DAX Expression for Dynamic Ranking
You will now create a DAX expression to rank a dimension based on the user’s selected measure. There are likely many ways this could be done but I’ll show you how I do it and explain along the way.
- Create a new measure in your Measures Table and give it a descriptive name like “Rank Measure”. In this measure, create a variable for the user’s selected measure value in your Measures Parameter table. Make sure to use the “Dummy” column – the original column won’t work.
- On a new line, create a variable for just one of the measure parameters using RANKX. The image below shows an example with a generalised version as a comment.
- Write (or copy) a similar ranking variable for all other measure parameters and type RETURN on the next line.
- After the RETURN, construct a SWITCH formula linking the selected variable options to the appropriate rank variables. Notice that in the screenshot below, the same spelling and case from the Dummy column is used in quotes. This ties everything together.
If you’re following along (and everything is working), you have completed the most difficult step in this process. Altogether, the new measure should look something like this:
Step 3 – Create a Table of Top N Values
In the ribbon, select Enter data and create a column of integer values in the pop-up window. Give the column and table a descriptive name, like Value and Top N Parameter.
This column will simply let users select a number for the Top N filter we will add to the report.
Note: unfortunately, the drawback here is that if you wanted users to be able to select high values, you would have to manually type in each number (or limit their options by going up in 5’s or 10’s)
Step 4 – Create Top N Filter Formula
In your Measures Table, create a new measure and write the following expression:
In simple terms, this expression assigns a value of 1 to items whose rank is less than or equal to the value users select from the column we just made (which will be added to the report). This will be used in the next step.
Step 5 – Filter Chart using Top N Filter Formula
In the Report view, select your chart, open the Filters pane, and drag the Top N Filter from your Measures Table to the “Add data fields here” space under Filters on this visual. Construct the filter to show items when the value is 1 and click apply.
Don’t panic if the chart’s data disappears. Click on Slicer in the Visualizations pane to add a slicer to your report. Now drag Value from the Top N Parameter table into the slicer. Format the slicer so only 1 value can be selected (Slicer settings > Options > Style > Dropdown).
That’s a lot of instruction, so here is a video demo for this full step:
Summary
Creating a dynamic Top N filter based on a selected measure is not the easiest task in Power BI. However, incorporating this functionality into a report can reduce repetitive charts whilst also allowing users to focus on the most significant items in the data based on specific measures. Remember to use effective and memorable naming conventions to make tasks like this easier and less prone to errors when constructing all necessary expressions.
