FORMAT bug

[Rudz]

According to the documentation, the FORMAT function takes as input "A custom format expression for numbers can have from one to three sections separated by semicolons." If three sections, "The first section applies to positive values, the second to negative values, and the third to zeros." Further, you can use a comma as a decimal placeholder. This all is fine, and as documented, behaves much like Excel formatting.

The problem is that when using a comma as a decimal placeholder, the three sections no longer apply to positive and negative values. If the positive or negative values evaluate to zero, the zero format is used instead. This behavior is undocumented and contrary to Excel formatting rules, which strictly apply the formats to positive and negative numbers regardless of rounding to zero.

This is a major problem because accountants and statisticaians know that 0 vs. (0) vs. "-" in Excel all mean different things. The 0 is a number rounded down to zero, (0) rounded up to zero, and "-" is a "pure" zero without rounding. As we are migrating reports to Power BI, our users are rejecting Power BI because they insist on having these formats. And I'm not looking forward to fixing this mess.

You can replicate this using this measure in Power BI:

Format bug = "Positive: " & FORMAT(1000000, "#,##0,,.0M;-#,##0,,.0M;-")
           & " Negative: " & FORMAT(-1000000, "#,##0,,.0M;-#,##0,,.0M;-")
           & " Zero: "     & FORMAT(0, "#,##0,,.M;-#,##0,,.M;-")

Gives the correct result of 3 different formats:

But if the positive and negative numbers round to zero, they incorrectly use the zero format:

Format bug = "Positive: " & FORMAT(1000, "#,##0,,M;-#,##0,,M;-")
           & " Negative: " & FORMAT(-1000, "#,##0,,M;-#,##0,,M;-")
           & " Zero: "     & FORMAT(0, "#,##0,,M;-#,##0,,M;-")

Or more blantantly:

Format bug = "499,999 in millions = " & FORMAT(499999, "#,##0,,M;-#,##0,,M;0???")

This bug either needs to be corrected, or PLEASE update the documentation to describe this non-sensical behavior. 

"The first section applies to positive values, the second to negative values, and the third to zeros except when they don't..."

[OwenAuger · Accepted solution]

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

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]

---

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

[Rudz]

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)"),"-"))

[OwenAuger]

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.

[Rudz]

We have a complex formatted KPI table that can't quite be created with the New Card visual. Our workaround is generating HTML from Excel and using the HTML visual. When you include a measure in the HTML, it appears as an unformatted number, so FORMAT is needed. I just wished it worked correctly!