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

Remi Øvstebø on 16 Aug 2020 03:56:06

RE: Conditional formatted measures using SWITCH

This feature would be so awesome :)

Matt Allington's profile image Profile Picture

Roberto De Santis on 16 Aug 2020 03:55:21

RE: Conditional formatted measures using SWITCH

I hope that this feature will be available also by using if-then-else. Instead of using format DAX function, I would like that it will be introduced a function to return data with a specific format (integer, floating point with specified format, currency, percentages, etc.).

Matt Allington's profile image Profile Picture

Power BI User on 16 Aug 2020 03:55:15

RE: Conditional formatted measures using SWITCH

This would be very useful. I have a visual that shows a bar graph of income by client with two switchable linear values superimposed on the bar graph--one for average billable rate and the other for total hours billed. This issue prevents the data labels on the lines from being properly formatted and really detracts from the chart.

Matt Allington's profile image Profile Picture

David Cresp on 16 Aug 2020 03:55:05

RE: Conditional formatted measures using SWITCH

This would be a great feature for a dashboard that I am working on. Lets hope this one gets picked up.

Matt Allington's profile image Profile Picture

Matt Allington on 16 Aug 2020 03:54:58

RE: Conditional formatted measures using SWITCH

'@Cheries Mewengkang, what you described will render the number as a text string in a table. This is not the same as changing the number format. If you change the number to a text string, you can't use it in charts or any other place that requires numbers (eg calcs)

Matt Allington's profile image Profile Picture

Cheries Mewengkang on 16 Aug 2020 03:54:16

RE: Conditional formatted measures using SWITCH

Yes you can control the format. Use FORMAT () function.

myMeasure = SUMX(MeasureTable,switch([selected measure],
1,[Total Sales],
2,[Total Cost],
3,[Total Margin],
4,FORMAT([Chg Sales vs LY %],"#,##0.00%")
))

Matt Allington's profile image Profile Picture

Tarun Rodrigues on 16 Aug 2020 03:54:07

RE: Conditional formatted measures using SWITCH

WORKAROUND 'alerts', aka NEAT HECK

I've used this scenario a few times. In your example, create 2 card visuals overlaying each other.
On the first card visual set the visual filters to filter on the 1st 3 dimension value to show $.
On the second card visual set the visual filter to filter on the % dimension value that will show %.

It's like an ON/OFF switch :)

Matt Allington's profile image Profile Picture

Power BI User on 16 Aug 2020 03:54:03

RE: Conditional formatted measures using SWITCH

Great idea, I would love to see this!

Matt Allington's profile image Profile Picture

Power BI User on 16 Aug 2020 03:53:59

RE: Conditional formatted measures using SWITCH

Even more than just the percentage vs. currency, there are lots of situations where you might want text or numbers, especially for error/out of bounds handling where this would be very helpful.

Matt Allington's profile image Profile Picture

Ivan Bondarenko on 16 Aug 2020 03:53:56

RE: Conditional formatted measures using SWITCH

Must have, agree. Have several scenarios where it would be the best solution.