Dynamic Measures/Titles (Using SWITCH)
06-24-2017 15:29 PM
06-24-2017 03:29 PM
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.
11-28-2018 01:54 AM
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?