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.

602 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 →

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

      • Anonymous commented  ·   ·  Flag as inappropriate

        We also need Custom Number format. So that we can use our own number formats just like excel.

      • David Cresp commented  ·   ·  Flag as inappropriate

        It would be great if this could get some more votes. It is a really necessary change that would greatly assist with formatting in visuals. Please support this one so that we can get Microsoft to look at this.

      • Roberto commented  ·   ·  Flag as inappropriate

        I hope that this feature will be available also by using if-then-else. Instead of using format DAX function, I would like that it will be introduced a function to return data with a specific format (integer, floating point with specified format, currency, percentages, etc.).

      • Anonymous commented  ·   ·  Flag as inappropriate

        This would be very useful. I have a visual that shows a bar graph of income by client with two switchable linear values superimposed on the bar graph--one for average billable rate and the other for total hours billed. This issue prevents the data labels on the lines from being properly formatted and really detracts from the chart.

      • David Cresp commented  ·   ·  Flag as inappropriate

        This would be a great feature for a dashboard that I am working on. Lets hope this one gets picked up.

      • Matt Allington commented  ·   ·  Flag as inappropriate

        @Cheries Mewengkang, what you described will render the number as a text string in a table. This is not the same as changing the number format. If you change the number to a text string, you can't use it in charts or any other place that requires numbers (eg calcs)

      • 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 :)

      ← Previous 1

      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.