Skip to main content

Power BI

Declined

Conditional formatted measures using SWITCH

Vote (4285) Share
Matt Allington's profile image

Matt Allington on 16 Jul 2016 04:20:35

A current limitation in Power BI SWITCH measures (and all Power Pivot actually) is that a SWITCH measure must have a single format. It is currently not possible to conditionally format the measure result based on any criteria - it is one single format only. There are valid use cases where you may want to change the format of the SWITCH measure depending on the result. Consider the following SWITCH statement
myMeasure = SUMX(MeasureTable,switch([selected measure],
1,[Total Sales],
2,[Total Cost],
3,[Total Margin],
4,[Chg Sales vs LY %]
))
The first 3 results are all currency format, but the last result is a percentage format. This currently can't be controlled. I would like to see an optional 3rd parameter in the SWITCH statement to set an alternate number format.

Administrator on 18 Mar 2022 20:00:41

Dynamic format strings are available in calculation groups and can be used to create a DAX expression to handle any format requirement for a pick a measure scenario such as this.

Documentation for dynamic format strings is available at https://docs.microsoft.com/analysis-services/tabular-models/calculation-groups?view=asallproducts-allversions#dynamic-format-strings-for-currency-conversion. And information on external tools to add calculation groups is available at https://aka.ms/externaltools.

There are multiple ways to utilize calculation groups and dynamic format strings and thank you to those who posted solutions in the chat.

Comments (76)
Matt Allington's profile image Profile Picture

Matt Allington on 16 Aug 2020 03:53:55

RE: Conditional formatted measures using SWITCH

Here is a simple demo of the problem
http://exceleratorbi.com.au/wp-content/uploads/2016/07/switch-measure.gif

Matt Allington's profile image Profile Picture

Frank on 16 Aug 2020 03:53:55

RE: Conditional formatted measures using SWITCH

This change would make things a lot simpler and the switch option way more powerful and useful

Matt Allington's profile image Profile Picture

Chris Turnbull on 16 Aug 2020 03:53:55

RE: Conditional formatted measures using SWITCH

Great idea - I use this feature a lot on a Power Pivot Disconnected Slicer and it would be great to be able to do the same with Power BI.

Matt Allington's profile image Profile Picture

Hans Mostafavi on 16 Aug 2020 03:53:55

RE: Conditional formatted measures using SWITCH

100% agree

Matt Allington's profile image Profile Picture

Daniel Otykier on 16 Aug 2020 03:53:54

RE: Conditional formatted measures using SWITCH

An option to "inherit" the formatting of the source measure would be nice. But it would be even better if a more general solution was provided, similar to how SCOPE statements work in SSAS multidimensional.

Matt Allington's profile image Profile Picture

Sohail on 16 Aug 2020 03:53:54

RE: Conditional formatted measures using SWITCH

Must have
Regards
sohail