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...
AngelaB's avatar
AngelaB
Icon for Helper I rankHelper I
2 years ago

Hello DallasBaba 

 

Thank you for continuing to help with this. I tried that edit but same result, it's returning all as "null". Does the order of expressions matter? I tried to reorder so that 0% was being recognised first, but couldn't get it to accept the FORMAT within the IF statement.

 

Ange

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?

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

    Hi again DallasBaba . Are you still willing to look at the .pbix file for this? I have tried ChatGPT and enlisting the help of someone with more Power BI experience than I have but still no luck.

     

    I'm unable to attached the .pbix file and linked .csv but here is a DropBox link - https://www.dropbox.com/scl/fo/zyhko4e7zr0ucxtdv453i/h?rlkey=z2f4bum5ybcb48eje5pdi4vyu&dl=0 

     

    In the above DAX that I was posting, [Value A] = [Mind Active] and the calculated measure is [%COG_IMP].

     

    Thanks for your help, I'm still convinced there has to be a simple solution!

    Angela