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!
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 )tonymaclaren
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.
Message 50 of 53 273,669 ViewsSean
Community ChampionYou 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.
Message 51 of 53 273,661 Views Regular VisitorThe COALESCE idea was great. But, how can I hide the months before the start date and the months after the last date?
Message 52 of 53 246,490 Views Frequent VisitorHi, did you ever figure out how to blank the values before the start date and after the end date?
Message 53 of 53 186,537 Views
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
Message 49 of 53 273,873 Views Frequent VisitorI 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.
