Conditional formatted measures using SWITCH
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],
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.
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."
This would be so helpful!
Stelvio Gori commented
Well, you can achieve that using FORMAT in your measure definition, e.g. (real example, nth member of a SWITCH clause):
values('KPI'[KPI]) = "VA/Ricavi"; format(divide(CALCULATE ( [Importo]; tb_CE[IdTopParent] = 1) +
CALCULATE ( [Importo]; tb_CE[IdTopParent] = 7);CALCULATE ( [Importo]; tb_CE[IdTopParent] = 1));"0.00%")
Andreas S. commented
come on - more than 1000 votes and no "we consider that on our roadmap" comment or something similar from the MS team???
It would be very helpful to support business needs to show both number and ratio format.
That would be so useful and would make the reports faster! I am working in a financial reports, and I was obligated to create an "Amount measure" and an "% measure" since i can not create just one u.u
Ivan Bondarenko commented
Would be awesome to have this. Currently, lack of this is a showstopper for using mentioned technique.
Alexis Olson commented
The FORMAT function sort of works, but means the result is text data type and values are rounded to precision displayed.
@Patrick, "FORMAT" function works for tables and Matrix, unfortunately STILL not working for e.g. stacked charts..Is there a reason we still have no answer from the team after 2 years?
Have any of you tried using the "FORMAT" function? It works.
This would be extremely helpful, please try and get this one out please.
This has been in request since two years and it is a very basic requirement as we do not have a global variable option too in Power BI. Looking forward to the team to include this flexibility.
Would be awesome! I could use this for enhancing interactivity in reports
David Cresp commented
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.
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.
Paresh Patel commented
This one will give more flexibility
David Cresp commented
This one is so simple but would make my dashboard formatting SO much better. Please MS take a look at this much-needed improvement.
Need to add this! This is a key feature for any business intelligence tool.
Tyler Burgess commented
Having this would great, allowing the ability to have dynamic measures!
Sean Cupolo commented
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.