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
'@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?
RE: Conditional formatted measures using SWITCH
Have any of you tried using the "FORMAT" function? It works.
RE: Conditional formatted measures using SWITCH
This would be extremely helpful, please try and get this one out please.
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.
RE: Conditional formatted measures using SWITCH
Would be awesome! I could use this for enhancing interactivity in reports
RE: Conditional formatted measures using SWITCH
This one should be simple and would add so much value to every dashboard that I do. Please MS bring this one in to PBI.
RE: Conditional formatted measures using SWITCH
I can't believe that this idea has not been implemented. The inability to conditionally format values with the SWITCH function is a severe limitation on what would otherwise be a very elegant and useful reporting function.
RE: Conditional formatted measures using SWITCH
This one will give more flexibility
RE: Conditional formatted measures using SWITCH
This one is so simple but would make my dashboard formatting SO much better. Please MS take a look at this much-needed improvement.
RE: Conditional formatted measures using SWITCH
Need to add this! This is a key feature for any business intelligence tool.