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 Thanks for letting me know, now let try

[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%",
            FORMAT(defined_var, "0%") // This line ensures the percentage is formatted correctly
        )
    )

The key change in this formula is using the FORMAT function to ensure the percentage is displayed as a percentage (e.g., "0%"). This should allow you to distinguish between null and 0% values in your [calculated%] column.

 

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

Thanks

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