topic Re: DAX command to differentiate between null value and zero in calculated column in DAX Commands and Tips

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

AngelaB — Tue, 27 Feb 2024 00:37:59 GMT

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!

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

DallasBaba — Wed, 18 Oct 2023 01:02:20 GMT

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

[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

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

AngelaB — Wed, 18 Oct 2023 01:46:23 GMT

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

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

DallasBaba — Wed, 18 Oct 2023 02:05:15 GMT

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!

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

AngelaB — Wed, 18 Oct 2023 04:00:16 GMT

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

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

DallasBaba — Wed, 18 Oct 2023 16:48:56 GMT

AngelaB Thanks for letting me know, now let try

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

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

AngelaB — Wed, 25 Oct 2023 03:21:47 GMT

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

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

DallasBaba — Wed, 25 Oct 2023 04:22:59 GMT

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

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

AngelaB — Wed, 25 Oct 2023 06:18:02 GMT

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?

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

AngelaB — Tue, 06 Feb 2024 05:51:50 GMT

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