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.
78 Comments
- 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.
- 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.
- This change would make things a lot simpler and the switch option way more powerful and useful
- Here is a simple demo of the problem http://exceleratorbi.com.au/wp-content/uploads/2016/07/switch-measure.gif
- Must have, agree. Have several scenarios where it would be the best solution.
- Must have Regards sohail
- 100% agree
- 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.
- Great idea, I would love to see this!
- '@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)
