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

Colin Banfield on 16 Aug 2020 03:59:44

RE: Conditional formatted measures using SWITCH

Mixing program logic with presentation logic is not a good idea. Since SWICH is syntax sugar for nested IFs, it is equivalent to adding a number format parameter to an IF statement. SWITCH is not the right place for setting conditional number formats. The correct place is in conditional formatting. At the time of this comment, Excel has had the capability for ten years.

Matt Allington's profile image Profile Picture

Chris on 16 Aug 2020 03:59:28

RE: Conditional formatted measures using SWITCH

Common problem and very important. Please add this!

Matt Allington's profile image Profile Picture

Nasir Shaikh on 16 Aug 2020 03:59:23

RE: Conditional formatted measures using SWITCH

We also need Custom Number format. So that we can use our own number formats just like excel.

Matt Allington's profile image Profile Picture

Mark Stacey on 16 Aug 2020 03:59:09

RE: Conditional formatted measures using SWITCH

This is also needed for multi currency, which is possibly a more common scenario

Matt Allington's profile image Profile Picture

Ashley on 16 Aug 2020 03:58:24

RE: Conditional formatted measures using SWITCH

Common problem and very important. Please add this.

Matt Allington's profile image Profile Picture

Hector Villafuerte on 16 Aug 2020 03:58:24

RE: Conditional formatted measures using SWITCH

It is possible to show different formats in the same column for a TableMatrix as shown below. In a Matrix use the formatting Values option "Show in Rows".

http://hectorv.com/table-or-matrix-for-side-by-side-column-comparison-with-columns-with-different-formats-in-the-same-column-updated

Matt Allington's profile image Profile Picture

David Cresp on 16 Aug 2020 03:58:06

RE: Conditional formatted measures using SWITCH

It would be great if this could get some more votes. It is a really necessary change that would greatly assist with formatting in visuals. Please support this one so that we can get Microsoft to look at this.

Matt Allington's profile image Profile Picture

Jörg Tiedemann on 16 Aug 2020 03:58:00

RE: Conditional formatted measures using SWITCH

It's a necessary thing that I need and it would solve a problem that I have often.

Matt Allington's profile image Profile Picture

John Moran on 16 Aug 2020 03:57:49

RE: Conditional formatted measures using SWITCH

This would be fantastic to have

Matt Allington's profile image Profile Picture

Simon Hobman on 16 Aug 2020 03:57:29

RE: Conditional formatted measures using SWITCH

Yes please, I need this.