Forum Discussion

AngelaB's avatar
AngelaB
Icon for Helper I rankHelper I
2 years ago

DAX command to differentiate between null value and zero in calculated column

Hello   **This post has been removed from Desktop thread and reposted here as I think may be more appropriate**   I've read a few posts along these lines and tried a few of the solutions but none...
DallasBaba's avatar
DallasBaba
Icon for Skilled Sharer rankSkilled Sharer
2 years ago

AngelaB 

you can use the ISBLANK function to check if the value is null, and then use the IF function to return a different result based on that.

The following will return BLANK() if [Value A] is null, and otherwise return the percentage of “Yes” values in [Value A]. You can then use different formatting options or conditional formatting to display the blank values differently from the zero values.

[calculated%] =
IF(
ISBLANK([Value A]),
BLANK(),
DIVIDE(
COUNTA(FILTER(Table, [Value A] = "Yes")),
COUNTA([Value A])
)
)

Or

[calculated%] =
IF(
    ISBLANK(CALCULATE(COUNTA([Value A], [Value A] IN { "Yes" })), [Value A])),
    "Null",
    IF(
        [Value A] = 0,
        "0%",
        DIVIDE(
            CALCULATE(COUNTA([Value A], [Value A] IN { "Yes" })),
            [Value A]
        )
    )
)

 

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

 

Let me know if this work
@ me in replies or I'll lose your thread!!!

 

Thanks

  • AngelaB's avatar
    AngelaB
    Icon for Helper I rankHelper I
    2 years ago

    Thanks for your help DallasBaba but I don't seem to be able to get that to work either. It now just returns all null and 0% values as 'null'.

     

    Here's the DAX that I ended up with in case you can see an error with what I've done?

     

    [calculated%] =
var [defined var] =
DIVIDE(
    (CALCULATE(
        COUNTA([Value A]),
        [Value A] IN { "Yes" })),  
        COUNTA([Value A]))
return
IF(
    ISBLANK([defined var]),
    "Null",
    IF(
        [defined var] = 0,
        "0%",
        [defined var]
        )
    )