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

Ben Moore on 25 Aug 2021 15:15:36

RE: Conditional formatted measures using SWITCH

You can use calculation groups but that is well in the weeds for most developers. I would love this functionality straight out of Power BI desktop.

Matt Allington's profile image Profile Picture

Sam Rock on 16 Aug 2021 03:18:54

RE: Conditional formatted measures using SWITCH

You can achieve multiple formatting using Calculation Group -
Create Calc Group instead of SWITCH measure (and a disjointed table)  and define the Formatting to be applied for each measure return. It works perfectly well.

Matt Allington's profile image Profile Picture

Samantha Jackson on 23 Feb 2021 11:04:11

RE: Conditional formatted measures using SWITCH

This would be very useful when working with multi currency data sources with multi currency options in the end report. SWITCH and FORMAT works for tabular display of data, but doesn't work with charts which do not seem to be able to handle the output as a number.

Matt Allington's profile image Profile Picture

Alan Stanford on 02 Feb 2021 20:54:23

RE: Conditional formatted measures using SWITCH

Please add a feature in Power BI to fix this problem.

I want to make my data currency with 0 decimals if $ is selected in a slicer but currency with 2 decimals if $ per widget is selected in the slicer. In order for a Matrix to automatically filter out Nulls, the format needs to be in a number format (Not Text).

Matt Allington's profile image Profile Picture

Jeffrey "Measure-Driven" Weir on 01 Feb 2021 22:02:57

RE: Conditional formatted measures using SWITCH

I guess Calculation Groups now allows this.

Matt Allington's profile image Profile Picture

Dainius Butkevičius on 08 Jan 2021 14:26:36

RE: Conditional formatted measures using SWITCH

I have read all comments in this tread and here is mine opinion:

1. Improved FORMAT() function could be solution, if this function would provide correct datatype & formatting. Not text as is now. (why, see Conditional formatting, Charts and etc.)
Master Measure:= IF(SelectedValue(Slicer[Value])=1, FORMAT([Measure1], "0,00"), FORMAT([Measure2], "0,0%"))

2. Solution could be to implement logic of inherent (parent) property in Measure tools => Format => (General, Currency, ...., Parent format).
It can be allowed only in case of stand-alone(pure) measure re-reference within IF(), SWITCH():
Master Measure:= IF(SelectedValue(Slicer[Value])=1,[Measure1], [Measure2])
In case of following := [Measure1] + [Measure2] - or similar, it should produce simple: Error in resolving parent formatting. Please select correct one.

3. What we then get (Yes, it’s more view & user experience):
3.1. Usage of Master Measure (MM) (which combines many measures (kpi) with help of IF()/SWITCH());
3.1. With help of slicer/visual in report user can select kpi in other visuals with MM (MM "reacts" on selected value in slicer);
3.2. In combination with parameter table(even sub-model) - where all measures of MM are listed - it’s possible to create:
a) groups and lists of kpi's / master kpi and child kpi's of group (off/on) / custom drilldown
b) user in slicer(if slicer and visual are based on parameter table field) can select needed kpi's, preferred layout, group of kpi's (depends on parameter table and slicer(s) in report);
c) parameter table allows to set kpi's allowed to see for the user (it is "row-level-security for the measure", especially if report visuals use only MM);
d) once report is developed, in some cases kpi's can be changed/added only with changes in MM and in parameter table.

I'm sure, other can add more ...
Hope, this will help us to get this nice feature faster! Vote !!!

Matt Allington's profile image Profile Picture

Dainius Butkevičius on 08 Jan 2021 12:05:42

RE: Conditional formatted measures using SWITCH

Hi,

this is excellent idea. Must have for PowerBI.

br,
Dainius B.

Matt Allington's profile image Profile Picture

sohail ansari on 21 Nov 2020 12:21:02

RE: Conditional formatted measures using SWITCH

One of the great Idea

Matt Allington's profile image Profile Picture

Rafał Kun on 08 Sep 2020 10:31:20

RE: Conditional formatted measures using SWITCH

Hi - this can now be done although it is a bit complex and has some limits.

Check my post here:
https://community.powerbi.com/t5/Desktop/Dynamic-formatting-of-measures-tutorial/m-p/1297373#M565099

Matt Allington's profile image Profile Picture

Alexandre Vacquerie on 16 Aug 2020 04:19:23

RE: Conditional formatted measures using SWITCH

needed thanks