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

[AngelaB]

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!

[DallasBaba]

@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]

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]

@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]

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

[AngelaB]

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?

[DallasBaba]

@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]

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

[DallasBaba]

@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]

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