Dynamic Measures/Titles (Using SWITCH)

12892 Views
Highlighted
Advisor
Posts: 24
Registered: ‎06-17-2017

Dynamic Measures/Titles (Using SWITCH)

This Quick Measure allows you to display a different measure in the chart depending on the selection in a slicer.

 

In the attached example, there are 4 possible measures which can be chosen: (i) Avg Quantity Ordered, (ii) Avg Product Cost, (iii) Avg Tax Amount and (iv) Avg Unit price.

 

These measure names are defined in a single-column paramter table (manually entered or could be created automatically by the Quick Measures wizard). This column/table is used as the slicer selection and the measure shown in the bar chart is then calculated as:

 

 

 

 

SelectedMeasure = IF(HASONEVALUE(tblParamMeasures[MeasureName]),SWITCH(VALUES(tblParamMeasures[MeasureName]),"Avg Unit Price",AVERAGE(fctSales[UnitPrice]),"Avg Quantity Ordered",AVERAGE(fctSales[OrderQuantity]),"Avg Tax Amount",AVERAGE(fctSales[TaxAmt]),"Avg Product Cost",AVERAGE(fctSales[ProductStandardCost])),AVERAGE(fctSales[UnitPrice]))

 

This expression needs a default option in the event that a single measure name has not been selected and in this case this is the Average Unit Price.

 

It is important for the chart to also have a dynamic title so that it is clear what is being displayed. The DAX expression for this could be something like:

 

Measure Title = IF(HASONEVALUE(tblParamMeasures[MeasureName]),VALUES(tblParamMeasures[MeasureName]),"Avg Unit Price") & " By Year -> Month -> Quarter -> Day"

 

It is important to ensure the same default option (in this case Avg Unit Price) is used.

 

 

Attachment
Regular Visitor
Posts: 43
Registered: ‎04-04-2017

Re: Dynamic Measures/Titles (Using SWITCH)

Thank you! This is very useful - I've been searching for an example like this!

Frequent Visitor
Posts: 5
Registered: ‎05-09-2018

Re: Dynamic Measures/Titles (Using SWITCH)

This is just what I've been looking for. 

It's a shame that theres no expression capability for titles on visuals.

Visitor
Posts: 1
Registered: ‎11-28-2018

Re: Dynamic Measures/Titles (Using SWITCH)

Hi,

 

Thanks for shairing this - it's great.

However I wonder if this is possible to use in LiveQuery mode where I can't create separate table for measure.

Do you have any workaround, suggested approach for this?

 

 

Cheers!