Forum Discussion

AngelaB's avatar
AngelaB
Helper I
2 years ago
Solved

Differentiating between null value and zero in calculated column

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's avatar
    Jihwan_Kim
    2 years ago

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

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    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's avatar
      AngelaB
      Helper I
      2 years ago

      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.