Dynamic Measures/Titles (Using SWITCH)

rsaprano — Sat, 24 Jun 2017 22:29:12 GMT

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.

eyJrIjoiY2I1NjJmYjQtNWIzNy00NTFkLTg3N2UtMTM4YmFmNjA2MDA3IiwidCI6IjBjNzk5ZDM4LTQ3NjQtNDJiYy1iNGZmLTIzYmViYTljN2ZlMiIsImMiOjh9

Re: Dynamic Measures/Titles (Using SWITCH)

wildmight2017 — Fri, 15 Sep 2017 14:53:14 GMT

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

Re: Dynamic Measures/Titles (Using SWITCH)

SQLArchitect — Wed, 03 Oct 2018 15:59:49 GMT

This is just what I've been looking for.

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

Re: Dynamic Measures/Titles (Using SWITCH)

Anonymous — Wed, 28 Nov 2018 09:54:54 GMT

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!

topic Re: Dynamic Measures/Titles (Using SWITCH) in Quick Measures Gallery

Dynamic Measures/Titles (Using SWITCH)

Re: Dynamic Measures/Titles (Using SWITCH)

Re: Dynamic Measures/Titles (Using SWITCH)

Re: Dynamic Measures/Titles (Using SWITCH)