Format dax produces blanks

Hi PowerBI Community, I am facing an issue with the FORMAT DAX function. I have a few measures that divide and produce the output in % form and I am using this output in the SWITCH DAX if accoun...

Need Help

danextian
2 years ago
Hi FatemaRangwala ,

If the referenced measure returns blank, I dont see any other reason for FORMAT to return as such. You can add + 0 or -0 after the referenced measure in your formula so it shows zero when blank.

= SWITCH ( MAX ( 'TableName'[account code] ), "account_code", FORMAT ( [measurename] + 0, "#0.00%" ), "account_code", [measurename] )

Alternatively, look into dynamic format strings so your measure still returns a number but formatted instead of text if FORMAT is used.

Alex87

Solution Sage

2 years ago

Hello,

True using this:

VAR _Condition = MAX('TableName'[account code])
VAR _Result = 
SWITCH(
    TRUE(),
   _Condition  = "account_code", FORMAT([measurename], "#0.00%"),
   _Condition = "another_code", FORMAT([measurename], "#0.00"),
 
    FORMAT([measurename], "#0.00%")
)
RETURN _Result

If it helps, please mark my reply as the solution. Thanks