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 18 Mar 2022 20:00:41
Dynamic format strings are available in calculation groups and can be used to create a DAX expression to handle any format requirement for a pick a measure scenario such as this.
Documentation for dynamic format strings is available at https://docs.microsoft.com/analysis-services/tabular-models/calculation-groups?view=asallproducts-allversions#dynamic-format-strings-for-currency-conversion. And information on external tools to add calculation groups is available at https://aka.ms/externaltools.
There are multiple ways to utilize calculation groups and dynamic format strings and thank you to those who posted solutions in the chat.
- Comments (76)
RE: Conditional formatted measures using SWITCH
This would be helpful to support multiple currencies support as well, since measures using the formatted data are not able to calculate. Are there any alternatives or work around for currency switch?
RE: Conditional formatted measures using SWITCH
Would really help to have this
RE: Conditional formatted measures using SWITCH
Will be a game changer
RE: Conditional formatted measures using SWITCH
Preferably also working from Tabular cube measures and formatting.
This is currently a huge disadvantage.
RE: Conditional formatted measures using SWITCH
Yes please I face the same problem!!!!
RE: Conditional formatted measures using SWITCH
Any news on this? It's a real pain point.
The alternative to this would be a measure picker in the power BI front end - then we could have a measure we could choose from (the latter is my preferred solution)
RE: Conditional formatted measures using SWITCH
Kind of like this:
https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36082540-measure-picker
RE: Conditional formatted measures using SWITCH
It's strange that this is almost at 2K votes and hasn't even received any sort of response from Microsoft. There are quite a few ideas with less votes that have received responses...but not this one.
RE: Conditional formatted measures using SWITCH
Does anyone know if this will be possible with the upcoming Expression-Based Formatting feature?
This is the description:
"Report authors need flexibility over how their visuals are formatted. As well as defining formatting through the formatting pane, a DAX expression can be used to set the formatting, giving full control over the business logic used to format the visual.
Authors can create rules that set the color of a KPI based on the progress towards a goal, set the style of a line on a chart based on which category is performing best, or any combination of these. Authors can even dynamically update the title of a visual based on selections made elsewhere in the report."
RE: Conditional formatted measures using SWITCH
This would be so helpful!