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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
o59393
Post Prodigy
Post Prodigy

Return zero when numerator is blank

Hi all

 

I am doing a dax to return a zero when the numerator is blank.

 

The problem I have is that after I create the mesaure, I get a bunch of zeros from dates that have no data. 

 

The original state is data from 1/1/2019 to 12/12019 complaints2.PNG

 

With the dax, I get data since 2017 up tp 2023, which is my date table.  complaints1.PNG

 

I tried when numerator is blank and denominator is <> than zero, then return a zero. Howver I get an error

 

Complaints = 
IF(ISBLANK(SUM(complaints[number_complaints])) && SUM(production[production]) <> "", 0 , 
(
(SUM (complaints[number_complaints]) / 
SUM(production[production]))) 
*VALUE(1000000) ) 

 

How can I get it right?

 

Thanks!

 

 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@o59393 

The problem is your second condition SUM(produc

tion[production]) <> "", <> is not equal to in DAX.
Do you want to make this condition not equal to blank? If so, try replace with

Not ISBLANK(SUM(production[production]))

Or you want some thing else?

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!




View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Try to use divide also.

 

Complaints = 

IF(ISBLANK(SUM(complaints[number_complaints])), 0 , 
(divide(
(SUM (complaints[number_complaints]) ,
SUM(production[production])),0) 
*VALUE(1000000) ) 

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

V-pazhen-msft
Community Support
Community Support

@o59393 

The problem is your second condition SUM(produc

tion[production]) <> "", <> is not equal to in DAX.
Do you want to make this condition not equal to blank? If so, try replace with

Not ISBLANK(SUM(production[production]))

Or you want some thing else?

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors