Forum Discussion

AngelaB's avatar
AngelaB
Helper 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 appear to be working. The situation is that I have a calculated column that returns a simple percentage that is a calculation of a filtered value:

 

[calculated%] = DIVIDE(    (CALCULATE(        COUNTA([Value A],        [Value A] IN { "Yes" })),          [Value A]))
 
This is then displayed by site/timepoint in a table and box and whisker plots. The trouble is that I need to be able to distinguish between null values (unable to calculate because there's no data to calculate a percentage from) and returned value of 0% as this contributes to mean/median etc.
 
I've tried adding +0 but this simply makes all null and 0% values the same. I also tried defining a variable and using an IF statement (maybe not very well!) but returns the same result:
 

return if(    not ISBLANK([defined variable]), [defined variable],    (if(        [defined variable] = 0, 0, BLANK())    ))

 

Here is a link to the .pbix file and .csv data (I'm unable to attach these files unfortunately) - https://www.dropbox.com/scl/fo/zyhko4e7zr0ucxtdv453i/h?rlkey=z2f4bum5ybcb48eje5pdi4vyu&dl=0 

 

Any tips from the brains trust? None of the solutions thus far have worked and ChatGPT has been unable to solve it either!

9 Replies

  • DallasBaba's avatar
    DallasBaba
    Skilled 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
      Helper 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]
        )
    )
  • DallasBaba's avatar
    DallasBaba
    Skilled Sharer
    2 years ago

    AngelaB you can  modify the furmula

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

    This formula should allow you to distinguish between null and 0% values in your [calculated%] column

     

    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!

     

    • AngelaB's avatar
      AngelaB
      Helper I
      2 years ago

      Thanks again, but same result I'm afraid, blank and 0 all return "null".

  • DallasBaba's avatar
    DallasBaba
    Skilled 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
      Helper 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
        Skilled 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