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
Having this would great, allowing the ability to have dynamic measures!
RE: Conditional formatted measures using SWITCH
Would truly add some flexible functionality. I have a matrix that has multiple measures that are inter-related (QTY, Unit Rate, Hours, Unit Price, etc. Putting them all on a matrix at the same time is confusing for the end user. Adding a slicer to select the measures that the user wants to see makes for a valuable analytic. Using Switch is the only current option. However, a better option would be to just build the functionality into the matrix and slicer essentially building a field filter.
RE: Conditional formatted measures using SWITCH
Power Bi has so many nice features, please add this it will be very helpful
RE: Conditional formatted measures using SWITCH
I've encountered this as well. I klugy work around is to return a formatted string for the measure. Not an ideal solution, I admit.
RE: Conditional formatted measures using SWITCH
Mixing program logic with presentation logic is not a good idea. Since SWICH is syntax sugar for nested IFs, it is equivalent to adding a number format parameter to an IF statement. SWITCH is not the right place for setting conditional number formats. The correct place is in conditional formatting. At the time of this comment, Excel has had the capability for ten years.
RE: Conditional formatted measures using SWITCH
Common problem and very important. Please add this!
RE: Conditional formatted measures using SWITCH
We also need Custom Number format. So that we can use our own number formats just like excel.
RE: Conditional formatted measures using SWITCH
This is also needed for multi currency, which is possibly a more common scenario
RE: Conditional formatted measures using SWITCH
Common problem and very important. Please add this.
RE: Conditional formatted measures using SWITCH
It is possible to show different formats in the same column for a TableMatrix as shown below. In a Matrix use the formatting Values option "Show in Rows".
http://hectorv.com/table-or-matrix-for-side-by-side-column-comparison-with-columns-with-different-formats-in-the-same-column-updated