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

Power BI User on 16 Aug 2020 04:19:23

RE: Conditional formatted measures using SWITCH

We need this feature in financial reports! Users have to be able to switch to actual values and values in thousands.

Matt Allington's profile image Profile Picture

Wade McGriff on 16 Aug 2020 04:19:22

RE: Conditional formatted measures using SWITCH

In Excel, you can change the format of a y-axis on a graph or the values of a pivot table value independently of the underlying source data. So similarly to being able to adjust the Display Units and decimal places of the labels and/or axis of a given visual you should also be able to edit the format independent of the underlying measure or column. Further to address this idea, you could allow expressions on this similar to titles which would solve the problem that we are having.

This would allow the underlying measure or column to remain as a number as opposed to text and therefore it would be "graphable" and then the graph or visual axis/label formatting could handle how it is displayed in terms of the format.

Update: looks like they are solving this with calculation groups, but i still like the idea above which would be an agile option for end users.

Matt Allington's profile image Profile Picture

John Thomas on 16 Aug 2020 04:19:21

RE: Conditional formatted measures using SWITCH

myMeasure = SUMX(MeasureTable,switch([selected measure],
1,{[Total Sales], type currency},
2,{[Total Cost], type currency},
3,{[Total Margin], Percentage.Type},
4,{[Chg Sales vs LY %], Percentage.Type}
))

Matt Allington's profile image Profile Picture

Aaron Morgan on 16 Aug 2020 04:19:17

RE: Conditional formatted measures using SWITCH

Please implement this idea. Would make our financial reporting much easier.

Matt Allington's profile image Profile Picture

Robert Slattery on 16 Aug 2020 04:18:10

RE: Conditional formatted measures using SWITCH

My view is that this would be solved if the FORMAT function had Left, Center and Right alignment and padding. Maybe this would be simpler?

Matt Allington's profile image Profile Picture

Amit Pradhan on 16 Aug 2020 04:17:33

RE: Conditional formatted measures using SWITCH

Still waiting for this to be implemented, this is a bummer for people who have to create financial reports that have different measures with different data types when using slicer measures

Matt Allington's profile image Profile Picture

Power BI User on 16 Aug 2020 04:17:33

RE: Conditional formatted measures using SWITCH

Same here

Matt Allington's profile image Profile Picture

Power BI Ideas Admin on 16 Aug 2020 04:17:32

RE: Conditional formatted measures using SWITCH

Year after years.. Damm why Power BI Team Why.. ?

Matt Allington's profile image Profile Picture

Lotta on 16 Aug 2020 04:17:17

RE: Conditional formatted measures using SWITCH

It is a nice thought that end-users can change the dimensions or measures that are visible in a chart on the fly.
But on fly is limited if it's not also applied to row formatting as described in this idea.
This is a huge limitation that will point end-users in a direction towards other tools that will have this abillity.

Matt Allington's profile image Profile Picture

Power BI Ideas Admin on 16 Aug 2020 04:16:38

RE: Conditional formatted measures using SWITCH

'+1