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 The order of expressions in the statement should not affect the outcome.

 

However, we can try a different approach by using a nested IF statements to ensure that the "0%" is recognized first. 

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

 

The above measure checks if defined_var is equal to 0%. If it is, It will return "0%." If not, the statement will proceed to check if it's blank and return "Null" if it is. Otherwise, It will format defined_var as a percentage.

 

This should ensure that "0%" is recognized before checking for null values.

 

If this doesn't work, please send the pbix file of your work sample with the existing relationship between the tables.

 

Thanks

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

Thank you, I've tried that now also and it just keeps returning whatever the first expression is in the IF statement, so now they're all displaying as 0% - this makes me think there is something that I need to do a step earlier to help Power BI recognise what is 0 and what is null?

 

This may be a potentially stupid question, but how do I provide the .pbix file without also providing identifiable information?