Dynamic format using DAX

Just a quick blog post today. Someone had a scenario where they wanted to have a different number of decimal points based  on the items on the rows. Initially they wanted to go with a calculated column but it is much easier to do this using the FORMAT function in DAX. Using the FORMAT function you can return the numerical value any way you want it.

Let’s take the following example. I have the several different devices and I want to format them all differently. I can write a single measure that return different formatting for each device.

This would be the measure I would write:

Measure = if(HASONEVALUE(Device[DeviceName]),
SWITCH(VALUES(Device[DeviceName])
,”Aircard 1″, FORMAT([Sum of Revenue], “0.0”)
,”Aircard 2″, FORMAT([Sum of Revenue], “0.00”)
,”Aircard 3″, FORMAT([Sum of Revenue], “$0.000″)
,”Aircard 4”, FORMAT([Sum of Revenue], “0.0000”)
,FORMAT([Sum of Revenue], “0”)
)
)

As you can see the results are what we expect:

Capture

You can also use this to show different symbols as you would use this when doing currency conversion.

One thing to observe here is that thanks to several performance optimizations the use of IF and SWITCH in Power BI desktop is significantly improved as compared to Power Pivot for Excel 2013 and SQL 2014.

  • Kasper in this way you return a string, which cannot be used in many places (filter, charts, …).

  • Jorge Esteves

    SQLArgonauts already tried the FORMAT solution – http://sqlargonauts.com/2012/12/02/formatting-time-intelligence-in-dax/ – but like C. Webb commented:

    “Although the Format() function returns text, which I guess could cause problems if you referred to the calculation in another calculation, in some cases.”

    And some other issues here : http://sqlargonauts.com/2013/01/14/dax-format-comes-back-to-haunt-us/

  • 看看!

  • 只需10元钱,4万G资源一键转存到你的360网盘

    囊括:

    网赚项目、名师讲座、设计素材、致富技术

    网络教程、文档资料、公开课,小说下载等等

    http://www.cao.eeequn.com/

    乙未年(羊)冬月廿六 2016-1-5

  • 【益群网:逆向网赚,坐等收钱】

    【系统优势:】
    优势1:静态分红,每日签到就有钱,每日最高一百元
    优势2:十级提成,逆向网赚,什么不干,照样有钱赚
    优势3:百万资源,永久更新,可一键转存到自己网盘

    【自动下滑:】
    每人限定5个一级下线,多推荐的自动下滑到一级下线名下
    从而成为推荐者的二级下线。以此类推,直至十级。
    无论这个下线是谁推荐的,你都有钱。
    你直接推荐的每个你提成5元,上线或下线推荐的每个你提成0.5元
    总之,多干多赚,什么都不干,照样有钱赚。

    免费注册网址:

    http://www.287947.eeequn.com/

  • GoodLuck!

  • 到此一游!

  • So Guys…. I get child level format and switch/if statements..fine….but what is the answer to have 1 parent measure that can return the different type of formats of its children measures. eg
    Measure A = Format(A, “percent”)
    Measure B = Format(B, “£.00”)
    Measure C = [measureA] + [measureB]

    Measure C returns an error for the reason you state as FORMAT returns a string. Yet i need different formatting depending on whether Measure C returns the % or £ result. any suggestions ?

    • Sorry I found my answer….. to use an if statement.
      if (%, [measure A], [measure B] )

    • I managed a workaround using an if statement. But now FORMAT generates a blank text value for all empty rows making the layout horribly. Any answers to remove the blank text rows of the measure?

  • Rahulinder Singh

    How to format blank to zero using FORMAT function?

  • Vitaly

    Hi Kasper, how were you able to keep the results right-aligned in the table/matrix after formatting? We are struggling this this topic because the formatted numbers get treated as strings and always get left-aligned.

    • Martin

      Vitaly – I think I have stumbled on the answer to your question. I was using this post to help with this problem, and saw the same behavior as you. My use case is in Power Bi and what I have observed is that this only seems to happen within tables. If you switch you table to a matrix then the values are right aligned. The values themselves though are still type ‘Text’ so they need to be handled appropriately in other formulas etc…

  • Pingback: #Excel Super Links #43 – shared by David Hager | Excel For You()

  • spotpuff

    This works for the table output but does not work for charts; is there any way to get this to work in charts? FORMAT() returns a text value and charts don’t seem to know how to read it.

    The issue I’m having is I want the user to be able to select between a value and a percentage display in Power BI, but as soon as you add FORMAT() the chart explodes.

    • Shawn A

      I had the same issue! The only way around this, as far as I can tell, is to wrap your statement in an IF(ISBLANK()) statement. Something like:

      IF (

      ISBLANK ( [Your Measure] ),

      BLANK (),

      FORMAT ( [Your Measure], “0.0%” )

      )

      • MichaelS

        Hi
        I am having this issue, but the ISBLANK makes no difference as the value returned is a text, so no reporting tool will use it. We used a similar method in multi-dimensional all the time which worked very well with all client tools, but Tabular just doesn’t work properly for formatting.
        Any ideas on a work around without just removing all formatting?
        Thanks
        Michael

        • Shawn A

          @disqus_74skNxFV7h:disqus To be honest, a couple weeks after I posted that solution, we ended up ripping it out and having no default formatting. It’s not ideal at all, but then at least the users can set it to whatever they want.