Dynamic filtering and Dynamic Visuals in Power BI

This blog will focus on building Dynamic Visuals for Sales Metrics. The primary focus in this blog is: (1) How to Empower the end-user with the option to select a metric such as Revenue or Profit, which will adjust the entire dashboard’s data. (2) Allow filtering for TopN products. Where N is the number such as 3, 5, and 10. Thus we have, To3, Top5, and Top10.

The expected outcome is the following: 

dynamic visual power bi multiple filtering

What this is doing, is allowing the user to dynamically view information over different measures. Creating dynamic visuals such as these, mean that your dashboard will be less crowded and perhaps even aesthetically more pleasing.

In this blog, I want to show you how you can achieve the above or even similar results by using various useful functions. So, keep reading if you want to see a stepwise solution for how the above can be achieved.

1. Setting up a dynamic Metric selection:

Steps:

  • First, build the Measures that you want to evaluate, i.e. Total Revenue, Total Profit, Total Cost, Total Quantity Sold.
    (This blog won’t cover the how-to for creating these measures, however, there are links at the end of this page where you can find methods for doing this).
  • Secondly, you can create a table that will be used for the slicer selections:

TableMetricSelection Power BI dynamic measure table

Call this table the “Metric Selection”

(The Metric Index column is created so that the slicers for the Metric can be sorted).

This Metric Selection table does not have relationships with any tables in the core data model.

  • Thirdly, after loading the Metric Selection table to the data model, you can choose to sort the Metric column according to the Metric Index Column. Do this by navigating to Column tools and selecting Sort by column, then select Metric Index.

Metric according to Metric index, power bi, dynamic filtering

  • Thereafter, create the slicer to show the dimensions; “Revenue”, “Profits”, “Costs” and “Quantity”. Do this by selecting the Metric column for your field and adding it as a slicer visual.

Slicer for metric selection in power bi, dynamic filtering

(This will be used to change the results in our visuals when users make a Metric selection).

(The primary purpose for this table is to be used to store the selections or measures and then feed it back to the calculations in the data model).

  • Store the selection made for the Metric:

Now we must assure that the selected Metric made by the user is stored inside a measure. This can be done relatively simply by making use of the SELECTEDVALUE function as follow

Metric Selected

  • We now need to have an expression that can evaluate the selected inputs made by the user and then for the one that results in TRUE, then that measure should be returned. For this, you can use the SWITCH function.
Selected Metric Measure using Switch

// We have set the default value to “Total Revenue”, for if nothing is selected, however, you can also make this BLANK.

  • The result of setting up this Dynamic Metric selection is that each time you select one of your metrics, all the data on your dashboard should change as seen below:

 

dynamic measure_Power BI Revenuedynamic measure_Power BI Profit
This method can also be applied to different time periods, which you will find in the link at the end of this page. 

2. Filter report with TopN Value selected

  • Create a TopN selector table and call it TopNTable:

TopNTable using RankX Power BI

This table shows the TopN Values that we want to evaluate.

  • Similar to the Metric Selection table, the TopNTable does not have relationships with any tables in the core data model.
  • Just like the Metric Selection table, after you’ve loaded the TopNTable to the date model, you can then go ahead and sort the TopN column by the TopNValue Column.
  • Create the slicer to show the dimensions; “Top3”, “Top5”, “Top10″. Revenue”, “Profits”, “Costs” and “Quantity”. Do this by selecting the TopN column for your field and adding it as a slicer visual.

TopN Slicer, Slicers, Power BI, Dynamic Visuals

  • Store the selected TopN Value,

Now we need to assure that the selected TopN Value made by the user is stored inside a measure. Which can be done by making use of the VALUES function as follow:

Selected TopN Value, Hasonevalue

<Note>

We used the HASONEVALUE function because this function will make sure that only one slicer is selected at a time.

Then we have one selection on the TopN slicer that evaluates to TRUE, and if not, then Blank.

  • Now that we have created a measure that stores the selection made by the user (SelectedTopNValue). We will use this measure in another measure.

(Using one measure in another is called measure branching.)

  • Next, we need to Create a Top N Product Sales measure, which will make use of the slicer selections to evaluate the TopN product by Product Name. To do this we will create the following measure: 

Top N Product Sales, RANKX

<Note>
‘Selected Metric’ checks which one of the ‘ Revenue’ or ‘Profits’ is selected.
RankingDimension checks the values for the products.
The calculation is done by referencing the measure (selected metric) and the selectedtopNvalue.
  • For this demonstration, the visual of choice for displaying the top N products is a Stacked bar chart. The Product Name is on the Axis field. And the Top N Product Sales measure is on the values field as seen below.
Power BI stacked bar chart

For a detailed explanation of the use of the DAX RANKX function in Power BI, please see our blog:

https://databear.com/how-to-use-the-dax-rankx-function-in-power-bi/

In conclusion,

There are so many great things that you can do with dynamic visuals and Measure branching.

I hope the above was helpful.

For links related to the inspiration of this blog topic please see below:

Creating your own measures:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-measures 

https://community.powerbi.com/t5/Desktop/Calculate-Gross-margin-in-Profit-and-Loss-statement/td-p/413791 

Deeper understand dynamic visuals and how to apply them over different time periods:

https://community.powerbi.com/t5/Community-Blog/Want-to-create-dynamic-visuals-that-make-your-analysis-really/ba-p/162282