During recent projects, we have been exploring Power BI, and in particular, investigating how to parameterise charts to increase interactivity. In this blog, I will cover how to dynamically switch measures and titles in Power BI. (Check out the next blog on switching dimensions as well!)

During my learning and preparation for this blog, I used mainly two resources:


 

1. Create measure name table to use in slicer

First, create a new single-column table that contains the measure names. I did this by manually entering in the names as new data. For this example I will use sales, profit and number of customers.

 

Use this measure names column as the slicer selection in your dashboard. A slicer filters the data displayed in all other charts in the report. You can configure the slicer to display as either a dropdown or buttons in the orientation settings (vertical and horizontal respectively).

 

2. Create a calculation to switch the measures

To create a calculation to switch the measures, it is necessary to first understand a few basic DAX expressions:

  • IF(<logical_test>,<value_if_true>, <value_if_false>)
    Checks whether the first argument is true, and returns a value accordingly
  • HASONEVALUE(<columnName>)
    Returns true when the column has been filtered down to one distinct value only
  • SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
    The switch function acts similar to an IF/ELSEIF or CASE statement. If the expression returns ‘value 1’, then evaluate expression 1, else if ‘value 2’ then expression 2, else…
  • VALUES(<TableNameOrColumnName>)
    If the input is a column name, it returns the distinct values from that column. If the input is a table name, it returns the rows from that table.

So a calculation to switch the measures could look something like the following. It can then be used as the value field of whichever chart you’re interested in.

SelectedMeasure =

IF(

HASONEVALUE(MeasuresTable[Measures]),

SWITCH(

VALUES(MeasuresTable[Measures]),

“Sales”, SUM(Orders[Sales]),

“Profit”, SUM(Orders[Profit]),

“Customers”, DISTINCTCOUNT(Orders[Customer Name])

),

SUM(Orders[Sales])

)

So essentially we are saying, look at what has been selected in the measures table with the slicer. If “Profit” then return the sum of profit, else if “Customers” then the number of customers, etc. Do this when one measure has been selected, otherwise by default return the sum of sales.

 

3. Create a dynamic title

It may be helpful to also create a dynamic title for the chart to describe the view. This can be done by simply pulling the value from the measures table. You could customise the title further using & and “<text>”. For example:

Measure Title =

IF(

HASONEVALUE(MeasuresTable[Measures]),

VALUES(MeasuresTable[Measures]),

“Sales”

)

& ” by Category”

Make sure you indicate the same default option from the earlier calculation. This calculation can then be used in the chart title.

 

And there you have it!

 

→ How to switch dimensions dynamically in Power BI

Image source: Racool_studio (freepik.com)

 

Danica Hui
Author: Danica Hui