cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ukeasyproj
Helper II
Helper II

show items with no data as 0 instead of blanks

temp 1.PNG

 

Just a little background on the above table:

 

Project Name, Category Name, and Portfolio Name are from a table called 'Projects"

 

Actual, Committed are from a table called 'Expenses'

 

Projects is a one to many relationship with Expenses

 

In the above scenario, the project "cancer test project 5" has no related records in the Expenses table, so it is showing as blank, is there a way to show 0 instead

 

I have tried doing the following in the Projects table, but it gave unexpected results:  Actual = IF(ISBLANK(SUMX(RELATEDTABLE('Expenses'), [Actual])) = FALSE(), SUMX(RELATEDTABLE('Expenses'), [Actual]), 0)

 

 

2 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

@ukeasyproj

 

Hi, please try with this Dax Formula in a measure:

 

ActualM =
IF (
    CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( Expenses[Actual] ) )
)

  




Lima - Peru

View solution in original post

Sean
Community Champion
Community Champion

Even easier would be to add zero to your Measure

Then you don't need an IF statement to check if the expression returns BLANK ( )

So if your SUMX Measure works get rid of the IF statement and just add " + 0  "at the end

same with if you use any other expression (something like this for example)

Measure = CALCULATE ( SUM (table[column] ), FILTER (... ) ) + 0 

This will ensure you get a 0 when its blank!

Good Luck! Smiley Happy

 

UPDATE: March 2020

New DAX COALESCE function - returns the first argument that is not blank!

If all arguments return blank then COALESCE returns blank as well!

So if you need a zero returned and not blank and your Measures don't address the blanks on their own

Add a zero as the last argument in case all Measures return blanks!

 

COALESCE ( [Measure1], [Measure2], 0 )

 

View solution in original post

48 REPLIES 48
tonymaclaren
Helper I
Helper I

Hi 

There is  an even cleaner solution using the new COALESCE Dax command. No CALCULATE required:

ActualM :=
COALESCE ( SUM ( Expenses[Actual] )0 )   

You can make it even more powerful:

	
COALESCE ( [MyMeasure], [DefaultMeasure] )

 Please mark as a solution.

Sean
Community Champion
Community Champion

You do realize that your "even more powerful" solution could still return blank right?

Unless you add a zero as a 3rd argument or each of your 2 measures address how to handle blank on their own.

 

Go back and read the paragraph right under the "even more powerful" solution you copied from here...

https://www.sqlbi.com/articles/the-coalesce-function-in-dax/ 

 

 

Hi,

 

The COALESCE idea was great. But, how can I hide the months before the start date and the months after the last date?

 

ericcamingao_0-1618385906942.png

Thank you.

Hi, did you ever figure out how to blank the values before the start date and after the end date?

Anonymous
Not applicable

Hi, 

 

Is there any idea how to reflect "0" when there is a list of countries in the table, but one country has no results at all? 
The goal is to show that all countries have smth even if it just zero, so the bad result would push it to add some products too. 

 

I cannot even see the country in the table I get from database via Power bi, but i know it is there 🙂 

mdawesdigitalk
Frequent Visitor

 

I also have a similar issue that I just can't get my head around..and the "+0" method hasn't solved it.

 

I have a complex model, but for this purposes have stripped it backed to simple basics, and still can't resolve it, even though I have previously I think.

 

In simple terms we have a customer base which is not active in every month on every product.

 

Therefore when comparing one month of Sales with another, you get blank entries in the database as part of the dataload relating to Customers, such as follows (removing the product variable to keep things even simplier);

 

1 table (DATA) with 3 Members as follows Customer, Month and Sales

 

Cust1, Month1, 100

Cust1, Month2 ,200

Cust1, Month3, 50

Cust2, Month1, 75

Cust2, Month3, 40

Cust3, Month2, 78

Cust3, Month3, 80

 

So Cust1 is active is all Months, Cust2 active in all months but Month2, and Cust3 active is all months but Month1.

 

My issue is that I'm trying to generate a simple Sales Variance (current month - previous month) report across all months for all Customers.  My totals balance, but the analysis by Customer is missing rows where when the Customer is not active in the "Current Month" - example table below for Month 2 as Current Month

 

Cust1 CntMth = 200, PrvMth = 100, SalesMove = 100

Cust3 CntMth = 78, PrvMth = 0, SalesMove = 78

Totals CntMnth = 278, PrvMnth = 175, SalesMove = 103

 

What is missing is the Cust2 line of CntMnth = blank (and so not included), PrvMnth 75, SalesMove = -75

 

[Note in my model I have actual dates of "01/09/2019, 01/10/2019, 01/11/2019" respectively but have kept things generic here.] 

 

The measures that I have used are as follows but the adding "+0" has not solved the issues as the blanks remain;

 

CntMnth = CALCULATE(sum(DATA[Sales]), DATEADD(DATA[Month],0,MONTH)) + 0

PrvMnth = CALCULATE(sum(DATA[Sales]), DATEADD(DATA[Month],0,MONTH)) + 0

SalesMove = CALCULATE([CntMnth] - [PrvMnth]) + 0

 

Any guidance, which I assume is very basic and simple, appreciated, as I've just got a total block on this, and shouldn't have...

 

Thanks

 

Mark

 

Sean
Community Champion
Community Champion

@mdawesdigitalk 

You don't need to add a zero to calculate current vs previous if that's all you are trying to do.

The Measures you've listed for current and previous look exactly the same?! (except for the Measure Names)

Dateadd only works if you have a list of consecutive dates so I would assume you have a calendar table.

Note that the Current and Previous Measures reference the Date column in the Calendar table in the example below that would be 'Date'[Date]

However, I would not use dateadd in this case.

Rather I would create these 4 Measures

 

Sales Amount = SUM ( Data[Sales] )

Current Month = TOTALMTD ( [Sales Amount], 'Date'[Date] )

Previous Month = CALCULATE( [Sales Amount], PREVIOUSMONTH ( 'Date'[Date] ) )

Cur - Prev = [Current Month] - [Previous Month]

 

 The final measure should provide you the result you are looking for.

 

Hope this helps! 🙂

Aks-1
Frequent Visitor

Hi @sphillips22 ,

                               Thank you so much for the response .It worked! . Although I have one question I am also looking at the items that are not present in the database ,I have a situation where there is calculated measure which falls in a specific quadrant (period) but since the data is not there in database ,It displays as Blank but I want to show or display it has 0.00.

 

What I am trying to say is when I export it into excel that period or record is not present in the database.

 

Please refer the screenshot:

 

error4.PNG

 

 

 

Any help is highly appreciated!

 

Thanks.

@Aks-1  for your question about the quadrants showing blanks, can you please post the dax formula that corresponds to it?

Hi @sphillips22 ,

                                Thank you so much for the response .The DAX formula I am using is 

CALCULATE( SUM(POC[Money_Paid]), FILTER( ALL(POC), POC[ACCTG_Period] <= MAX(POC[ACCTG_Period]) ), VALUES(POC[LY])+0

 

Please note for data that has no value in the row it displays as 0.00 as shown in the screenshot but since the header part is derived it does not have data in database which I would like to display as 0.00 instead of Blank.

 

 

Thanks.

@Aks-1  a little tough to determine without seeing the table defitions, however, I would start by removing the VALUES() clause at the end of the CALCULATE function. The new dax measure would read as below:

 

Measure:=

CALCULATE(

  SUM(POC[Money_Paid]),

  FILTER( ALL(POC), POC[ACCTG_Period] <= MAX(POC[ACCTG_Period]) )

) + 0

 

It may also be that you need to use the "+ 0" after the closing paranthesis of the CALCULATE function.

By utilizing the VALUES function, you are effectively filtering the CALCULATE function to apply to only the unique "LY" values in the POC table. This may be causing the undesired result of blank values, for those LY values that are not in POC table.

 

Let me know if that works for you.

 

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

                                   Please find an example that I have below which shows blank values in cells 9,12,30 & 33 ect as the data for these quarter periods there is no data or no record  is present in database ,the requirement is to fill these cells with 0.00 instead of keeping it as blank.

 

 

Table.PNG

 

Thanks

Vvelarde
Community Champion
Community Champion

@ukeasyproj

 

Hi, please try with this Dax Formula in a measure:

 

ActualM =
IF (
    CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (),
    0,
    CALCULATE ( SUM ( Expenses[Actual] ) )
)

  




Lima - Peru

Hi @Vvelarde
I am using the same logic its working as long as we have data coming from dimensional table.

If we add fields from 2 or more table its duplicating the data ?

how can we remove duplicates ?

 

thanks


 

ActualM = SUM ( Expences[Actual]) +0

 

Hi,

 

This was helpful. I followes this DAX but how can I hide the months before start month and after end month. They also appear with zeroes.

ericcamingao_0-1618385513003.png

Thank you.

Anonymous
Not applicable

Thanks it worked

Sean
Community Champion
Community Champion

Even easier would be to add zero to your Measure

Then you don't need an IF statement to check if the expression returns BLANK ( )

So if your SUMX Measure works get rid of the IF statement and just add " + 0  "at the end

same with if you use any other expression (something like this for example)

Measure = CALCULATE ( SUM (table[column] ), FILTER (... ) ) + 0 

This will ensure you get a 0 when its blank!

Good Luck! Smiley Happy

 

UPDATE: March 2020

New DAX COALESCE function - returns the first argument that is not blank!

If all arguments return blank then COALESCE returns blank as well!

So if you need a zero returned and not blank and your Measures don't address the blanks on their own

Add a zero as the last argument in case all Measures return blanks!

 

COALESCE ( [Measure1], [Measure2], 0 )

 

Adding +0 or COALESCE is effecting visuals and getting error visuals exceded the available resources

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors