Showing results for 
Search instead for 
Did you mean: 

Dynamic Measures/Titles (Using SWITCH)


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.



wildmight2017 Regular Visitor
Regular Visitor

Re: Dynamic Measures/Titles (Using SWITCH)

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

SQLArchitect Frequent Visitor
Frequent Visitor

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.

matgul Visitor

Re: Dynamic Measures/Titles (Using SWITCH)



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?