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:
You can also use this to show different symbols as you would use this when doing currency conversion. One thing to note here as well is that all values get returned as string to Power BI, this means you can only use this “trick” with tables and not on charts.
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, …).
@Kasper, @Marco
do you know any other method which allow us to keep the same functionality without conversion values to string data type?
disadvantages of above method:
– cannot be presented as visual, filter etc as Marco pointed out
– Blank values are converted into empty string, which require additional handling of IF( ISBLANK ( …
– I suspect that there could be some issues with export such a matrix to .csv file
My scenario:
– User have possibility to choose measure from the slicer
– based on the user choice, measure should be presented as decimal, percentage and so on
maybe calculation groups could be helpful here somehow?
thank you guys for all what you do for the community,
Regards!
Yes with calculationgroups you can do this.
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/
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?
How to format blank to zero using FORMAT function?
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…
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.
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%” )
)
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.
I need to reformat a value of 0.74 to a percentage using DAX but i cant figure out the formula can anyone help?
You should change the measure format to % on the modelling tab.
Still no solution for dynamic formatting within a graph/tool-tip?
Anyone has any solution for this kind of switch measure with Formatting to use in Charts?
Unfortunately that doesn’t work 🙁
Thank you.
The fact that this is still not an available as a non-text option is so frustrating!!
agreed unfortunately it is not that easy to add.
Can someone explain where [Sum of Revenue] comes from in this example? I am trying to do this but when I get to the FORMAT part I can’t select the column from the table I am trying to format, only other measures that have already been created.
Hi Jodi,
It is a different measure I created before (seperatly)
Hope that helps,
Kasper
Is it still the same guys. As FORMAT returns a string value it cannot be used in charts. any solution yet or that is still not possible?
not yet but should be done later this year …
Can we use calculation groups in SSAS to show dynamic Formatting in Charts used with Switch condition
not yet but you will in the next few month.Only with calc groups though.
I have a situation where the decimal value needs to be truly dynamic based on the category. The model has prices for different commodities. If I am looking at Sugar, the price is in 2 decimal places, Bean Oil is in 3 places, and Canadian dollar price is in 5 decimal places(eg .75055). On the chart CDN dollar will show as .75
I have changed the decimal precision to 4 places to accommodate pricing of currencies, but Sugar price showing as 10.0200 is ugly.
In Tableau this is not an issue as it seems to handle it dynamically. In Power BI, I haven’t figured out how best to handle this.
Can you change the color of a measure as part of the format within a SWITCH Statement
Dude….you have no idea the impact this will have …THANK YOU!