Differentiating between null value and zero in calculated column

AngelaB — Tue, 27 Feb 2024 00:42:33 GMT

Hello

**I'm reposting this with the addition of a .pbix and .csv file as the original post (DAX command to differentiate between null value an... - Microsoft Fabric Community) has gone dead**

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 directly 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: Differentiating between null value and zero in calculated column

Jihwan_Kim — Tue, 27 Feb 2024 03:57:47 GMT

Hi,

I am not sure if I understood your question correctly, but please try something like below whether it suits your requirement.

%COG_IMP = VAR _a = CALCULATE ( COUNTA ( 'Interviews_All-Data'[Mind Active] ), 'Interviews_All-Data'[Mind Active] IN { "Yes" } ) VAR _b = COUNTA ( 'Interviews_All-Data'[Mind Active] ) RETURN IF ( _a = 0 && _b <> 0, 0, DIVIDE ( _a, _b, BLANK () ) )

Re: Differentiating between null value and zero in calculated column

AngelaB — Sun, 03 Mar 2024 21:45:54 GMT

Yes, this worked! Thank you, I knew there had to be simple solution but just couldn't get there. Thanks so much for your help.

topic Re: Differentiating between null value and zero in calculated column in DAX Commands and Tips

Differentiating between null value and zero in calculated column

Re: Differentiating between null value and zero in calculated column

Re: Differentiating between null value and zero in calculated column