CODEWITHSUNDEEP
powerbidax
jjah
jjah

Reputation: 71

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

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

Upvotes: 5

Views: 13976

Answers (3)

Priyanka2304
Priyanka2304

Reputation: 210

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

Upvotes: 6

mkRabbani
mkRabbani

Reputation: 16908

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

Upvotes: 2

Murray Foxcroft
Murray Foxcroft

Reputation: 13745

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

Upvotes: 1

Related Questions