i need to replace NaN with blank space where the operation fails in dax power bi

user_12168699 0
Viewed 4545

The table is like the image:

enter image description here

I need to keep the NaN as empty but when I use IFERROR() and put blank() in the ValueifError it just deletes the entire row which I dont want. Is there any way to replace NaN with a blank space

I used the dax below: oscar wins = SUM(Films[OscarWins])/SUM(Films[OscarNominations])

dax powerbi
3 Answers

Try using the DIVIDE function instead of '/' operator. Ex: Test = DIVIDE(Col1,Col2)

user_99167130

You can handle the case when denominator is 0 as below. This will simply check, if the denominator is 0, it will return BLANK(). Other case it will return the result from normal calculation.

oscar wins = 
IF(
    SUM(Films[OscarNominations]) = 0,
    BLANK(),
    SUM(Films[OscarWins])/SUM(Films[OscarNominations])
)
user_36523450

It is likely your SUM(Films[OscarNominations]) is returning 0, resulting in a divide by zero.

For your IFERROR fix, right click on a field value in your visual and select "Show items with no data".

enter image description here

Alternatively, on error, return 0. It really depends on how you want your audience to interpret the data. In reality, it is Not A Number (NaN)...

user_20611330
Related