3858
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.
STATUS DETAILS
Under Review
Ideas Administrator

From the comments and scenarios everyone's describing, the problem is really that you need a way to allow end-users to change which measure is displayed in a visual on the fly. We have upcoming plans for a dyanmic measures and dimensions feature; however, we do not have timeline to share at this time.

In the interim, the FORMAT function might help, as long as you're showing the results in text rather than a chart. https://docs.microsoft.com/en-us/dax/format-function-dax has details on how to use that. The expression-based formatting feature that we've started work on may help to a certain extent, but that's really designed to change a visual's properties rather than the data type or format. We'll look into extending that work to support this scenario as well. We're moving this to Backlog status - our core plan is to build ways that end-users can change the dimensions or measures that are visible in a chart on the fly. They'll be able to explore much more freely rather than relying on the author to have set up complex expressions to control what's available. We'll update with more details as they're available.

Comments

M

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.

M

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).

M

I guess Calculation Groups now allows this.

M

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 !!!

M

Hi,

this is excellent idea. Must have for PowerBI.

br,
Dainius B.

M

One of the great Idea

M

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

M

needed thanks

M

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

M

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.