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 20 Aug 2020 01:22:10
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. As a few folks have mentioned, the FORMAT function might help in the interim, 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 (70)
RE: Conditional formatted measures using SWITCH
It would be great if this could get some more votes. It is a really necessary change that would greatly assist with formatting in visuals. Please support this one so that we can get Microsoft to look at this.
RE: Conditional formatted measures using SWITCH
It's a necessary thing that I need and it would solve a problem that I have often.
RE: Conditional formatted measures using SWITCH
This would be fantastic to have
RE: Conditional formatted measures using SWITCH
Yes please, I need this.
RE: Conditional formatted measures using SWITCH
This feature would be so awesome :)
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.).
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.
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.
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)
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%")
))