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.

650 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    I agree to the terms of service
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    32 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      Submitting...
      • Cheries Mewengkang commented  ·   ·  Flag as inappropriate

        Yes you can control the format. Use FORMAT() function.

        myMeasure = SUMX(MeasureTable,switch([selected measure],
        1,[Total Sales],
        2,[Total Cost],
        3,[Total Margin],
        4,FORMAT([Chg Sales vs LY %],"#,##0.00%")
        ))

      • Tarun Rodrigues commented  ·   ·  Flag as inappropriate

        WORKAROUND ALERT, aka NEAT HECK

        I've used this scenario a few times. In your example, create 2 card visuals overlaying each other.
        On the first card visual set the visual filters to filter on the 1st 3 dimension value to show $.
        On the second card visual set the visual filter to filter on the % dimension value that will show %.

        It's like an ON/OFF switch :)

      • Anonymous commented  ·   ·  Flag as inappropriate

        Even more than just the percentage vs. currency, there are lots of situations where you might want text or numbers, especially for error/out of bounds handling where this would be very helpful.

      • Daniel Otykier commented  ·   ·  Flag as inappropriate

        An option to "inherit" the formatting of the source measure would be nice. But it would be even better if a more general solution was provided, similar to how SCOPE statements work in SSAS multidimensional.

      • Chris Turnbull commented  ·   ·  Flag as inappropriate

        Great idea - I use this feature a lot on a Power Pivot Disconnected Slicer and it would be great to be able to do the same with Power BI.

      • Brian Mather commented  ·   ·  Flag as inappropriate

        Going to have to find another idea to drop so I can vote for this! Brilliant idea and yes please!

      • Frank commented  ·   ·  Flag as inappropriate

        This change would make things a lot simpler and the switch option way more powerful and useful

      ← Previous 1 2 Next →

      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.