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
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.
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.