How can we improve Power BI?

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

1,110 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Matt Allington shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    46 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

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

      • victor commented  ·   ·  Flag as inappropriate

        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

      • Alexis Olson commented  ·   ·  Flag as inappropriate

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

      • attila commented  ·   ·  Flag as inappropriate

        @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?

      • Daniel commented  ·   ·  Flag as inappropriate

        This would be extremely helpful, please try and get this one out please.

      • Subin commented  ·   ·  Flag as inappropriate

        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.

      • David Cresp commented  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      • David Cresp commented  ·   ·  Flag as inappropriate

        This one is so simple but would make my dashboard formatting SO much better. Please MS take a look at this much-needed improvement.

      • Sean Cupolo commented  ·   ·  Flag as inappropriate

        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.

      • Paul Turley commented  ·   ·  Flag as inappropriate

        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.

      • Colin Banfield commented  ·   ·  Flag as inappropriate

        Mixing program logic with presentation logic is not a good idea. Since SWICH is syntax sugar for nested IFs, it is equivalent to adding a number format parameter to an IF statement. SWITCH is not the right place for setting conditional number formats. The correct place is in conditional formatting. At the time of this comment, Excel has had the capability for ten years.

      ← Previous 1 3

      Feedback and Knowledge Base

      Ready to get started?

      Try new features of Power BI today by signing up and learn more about our powerful suite of apps.