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
WORKAROUND 'alerts', aka NEAT HECK
I've used this scenario a few times. In your example, create 2 card visuals overlaying each other.
On the first card visual set the visual filters to filter on the 1st 3 dimension value to show $.
On the second card visual set the visual filter to filter on the % dimension value that will show %.
It's like an ON/OFF switch :)
RE: Conditional formatted measures using SWITCH
Great idea, I would love to see this!
RE: Conditional formatted measures using SWITCH
Even more than just the percentage vs. currency, there are lots of situations where you might want text or numbers, especially for error/out of bounds handling where this would be very helpful.
RE: Conditional formatted measures using SWITCH
Must have, agree. Have several scenarios where it would be the best solution.
RE: Conditional formatted measures using SWITCH
Here is a simple demo of the problem
http://exceleratorbi.com.au/wp-content/uploads/2016/07/switch-measure.gif
RE: Conditional formatted measures using SWITCH
This change would make things a lot simpler and the switch option way more powerful and useful
RE: Conditional formatted measures using SWITCH
Great idea - I use this feature a lot on a Power Pivot Disconnected Slicer and it would be great to be able to do the same with Power BI.
RE: Conditional formatted measures using SWITCH
100% agree
RE: Conditional formatted measures using SWITCH
An option to "inherit" the formatting of the source measure would be nice. But it would be even better if a more general solution was provided, similar to how SCOPE statements work in SSAS multidimensional.
RE: Conditional formatted measures using SWITCH
Must have
Regards
sohail