Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AngelaB
Helper I
Helper I

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!

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.