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

Stelvio Gori on 16 Aug 2020 04:08:12

RE: Conditional formatted measures using SWITCH

Well, you can achieve that using FORMAT in your measure definition, e.g. (real example, nth member of a SWITCH clause):
values('KPI'[KPI]) = "VA/Ricavi"; format(divide(CALCULATE ( [Importo]; tb_CE[IdTopParent] = 1) +
CALCULATE ( [Importo]; tb_CE[IdTopParent] = 7);CALCULATE ( [Importo]; tb_CE[IdTopParent] = 1));"0.00%")

Matt Allington's profile image Profile Picture

Andreas S. on 16 Aug 2020 04:07:55

RE: Conditional formatted measures using SWITCH

come on - more than 1000 votes and no "we consider that on our roadmap" comment or something similar from the MS team???

Matt Allington's profile image Profile Picture

Power BI User on 16 Aug 2020 04:07:46

RE: Conditional formatted measures using SWITCH

It would be very helpful to support business needs to show both number and ratio format.

Matt Allington's profile image Profile Picture

victor on 16 Aug 2020 04:05:58

RE: Conditional formatted measures using SWITCH

That would be so useful and would make the reports faster! I am working in a financial reports, and I was obligated to create an "Amount measure" and an "% measure" since i can not create just one u.u

Matt Allington's profile image Profile Picture

Ivan Bondarenko on 16 Aug 2020 04:05:53

RE: Conditional formatted measures using SWITCH

Would be awesome to have this. Currently, lack of this is a showstopper for using mentioned technique.

Matt Allington's profile image Profile Picture

Alexis Olson on 16 Aug 2020 04:05:52

RE: Conditional formatted measures using SWITCH

The FORMAT function sort of works, but means the result is text data type and values are rounded to precision displayed.

Matt Allington's profile image Profile Picture

attila on 16 Aug 2020 04:05:42

RE: Conditional formatted measures using SWITCH

'@Patrick, "FORMAT" function works for tables and Matrix, unfortunately STILL not working for e.g. stacked charts..Is there a reason we still have no answer from the team after 2 years?

Matt Allington's profile image Profile Picture

Patrick on 16 Aug 2020 04:05:17

RE: Conditional formatted measures using SWITCH

Have any of you tried using the "FORMAT" function? It works.

Matt Allington's profile image Profile Picture

Daniel on 16 Aug 2020 04:04:54

RE: Conditional formatted measures using SWITCH

This would be extremely helpful, please try and get this one out please.

Matt Allington's profile image Profile Picture

Subin on 16 Aug 2020 04:04:52

RE: Conditional formatted measures using SWITCH

This has been in request since two years and it is a very basic requirement as we do not have a global variable option too in Power BI. Looking forward to the team to include this flexibility.