Skip to main content

Power BI

Needs Votes

Conditional formatted measures using SWITCH

Vote (3436) Share
Matt Allington's profile image

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)
Matt Allington's profile image Profile Picture

b59dc44e 2beb-4634-9268-0063d985295e on 16 Aug 2020 04:10:16

RE: Conditional formatted measures using SWITCH

Kind of like this:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/36082540-measure-picker

Matt Allington's profile image Profile Picture

529d75a8 9355-4257-9ada-5b642792c5c3 on 16 Aug 2020 04:09:33

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.

Matt Allington's profile image Profile Picture

529d75a8 9355-4257-9ada-5b642792c5c3 on 16 Aug 2020 04:08:52

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

Matt Allington's profile image Profile Picture

22f9f1dc 74a0-4e59-a2d2-7cac164e1d22 on 16 Aug 2020 04:08:18

RE: Conditional formatted measures using SWITCH

This would be so helpful!

Matt Allington's profile image Profile Picture

39411882 6ac2-422f-bcc7-75ecc9d5b9ec on 16 Aug 2020 04:08:12

RE: Conditional formatted measures using SWITCH

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%")

Matt Allington's profile image Profile Picture

dbd9b92d 8010-4a1d-af1e-1610685e655c on 16 Aug 2020 04:07:55

RE: Conditional formatted measures using SWITCH

come on - more than 1000 votes and no "we consider that on our roadmap" comment or something similar from the MS team???

Matt Allington's profile image Profile Picture

bd9fe223 a0f4-48f4-a200-10cb8da09634 on 16 Aug 2020 04:07:46

RE: Conditional formatted measures using SWITCH

It would be very helpful to support business needs to show both number and ratio format.

Matt Allington's profile image Profile Picture

bfc7b8ba 4117-4393-b960-5755ea266ef7 on 16 Aug 2020 04:05:58

RE: Conditional formatted measures using SWITCH

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

Matt Allington's profile image Profile Picture

23309802 5327-4608-9a85-757be45ca59b on 16 Aug 2020 04:05:53

RE: Conditional formatted measures using SWITCH

Would be awesome to have this. Currently, lack of this is a showstopper for using mentioned technique.

Matt Allington's profile image Profile Picture

1b346d83 7901-4df8-8bbe-69b2a7d0da5a on 16 Aug 2020 04:05:52

RE: Conditional formatted measures using SWITCH

The FORMAT function sort of works, but means the result is text data type and values are rounded to precision displayed.