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.

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.

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 ?

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.

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…

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

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