Differentiating between null value and zero in calculated column

[AngelaB]

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!

[Jihwan_Kim · Accepted solution]

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

[AngelaB]

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.