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.
3 Replies
Thank you! This is very useful - I've been searching for an example like this!
This is just what I've been looking for.
It's a shame that theres no expression capability for titles on visuals.
