Forum Discussion
FORMAT bug
Rudz - this is indeed troubling.
I tested this myself on dax.do (Azure Analysis Services) and Power BI Desktop/Service, and see exactly the same behaviour as you've described.
The good news is that format strings (such as the one you posted) appear to behave correctly when applied as the format string of a numerical measure or column.
However, I share your frustration that FORMAT doesn't behave as documented, and for now we'll have to be extra cautious when using it. I'm happy to log this as a bug in the meantime.
Rudz - this is indeed troubling.
I tested this myself on dax.do (Azure Analysis Services) and Power BI Desktop/Service, and see exactly the same behaviour as you've described.
The good news is that format strings (such as the one you posted) appear to behave correctly when applied as the format string of a numerical measure or column.
However, I share your frustration that FORMAT doesn't behave as documented, and for now we'll have to be extra cautious when using it. I'm happy to log this as a bug in the meantime.
If you could log this as a bug, that would be amazing!
And thank you for pointing this out as a workaround for me:
OwenAuger wrote:The good news is that format strings (such as the one you posted) appear to behave correctly when applied as the format string of a numerical measure or column.
I'm working in a thin report conencted to a model, so changing formats means waiting for model changes, and the FORMAT function helps me take control of formatting in the report. But to meet this requirement I can wait for the model changes to get the #,##0;(#,##0);"-" part done, and then do the /1000000 part where I'm doing the FORMAT function and achieve the same thing.
Rudz wrote:I'm working in a thin report conencted to a model, so changing formats means waiting for model changes, and the FORMAT function helps me take control of formatting in the report. But to meet this requirement I can wait for the model changes to get the #,##0;(#,##0);"-" part done, and then do the /1000000 part where I'm doing the FORMAT function and achieve the same thing.
Nevermind. This doesn't work. The bug fix is my only answer withour adding IF statements to handle this.
Here's the workaround with IF:
Format workaround = VAR _Pos = 1000 VAR _Neg = -1000 VAR _Zero = 0 RETURN "Positive: " & IF(_Pos>0, FORMAT(_Pos,"#,##0,,;(#,##0,,);0"), IF(_Pos<0, FORMAT(_Pos,"#,##0,,;(#,##0,,);(0)"),"-")) & " Negative: " & IF(_Neg>0, FORMAT(_Neg,"#,##0,,;(#,##0,,);0"), IF(_Neg<0, FORMAT(_Neg,"#,##0,,;(#,##0,,);(0)"),"-")) & " Zero: " & IF(_Zero>0, FORMAT(_Zero,"#,##0,,;(#,##0,,);0"), IF(_Zero<0, FORMAT(_Zero,"#,##0,,;(#,##0,,);(0)"),"-"))Ah I see, yes if you're connecting to a remote model then ideally it is fixed there.
For what you're doing, do you have to return formatted numerical values within a text measure?
If you just need numerical measures, you can always create report-level measures that directly reference the original measures, but set up with the correct format string.