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)
Solved! Go to Solution.
Hi, please try with this Dax Formula in a measure:
ActualM = IF ( CALCULATE ( SUM ( Expenses[Actual] ) ) = BLANK (), 0, CALCULATE ( SUM ( Expenses[Actual] ) ) )
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!
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 )
First of all, dont use (values)/10 use DIVIDE(Values),10) or you could get a divide by zero error.
My Measure := VAR Values = SUM ( 'table name'[Column name] ) RETURN COALESCE ( DIVIDE ( Values, 10 ), 0 )
This will avoid the possibility of divide by zero, which is good practice and give you a zero if there is no data
Do you have a copy of you DAX formula, by chance?
It sounds like you could do a count against the day column and use that as your denominator.
Having to write a measure everytime you want to add a field (count, sum, etc) to a card is a poor solution. Not only do you have to create and mange the measures which provide virtually no value, but measures are a performance drain.
I know the measures wouldn't cause much of a drain, but if you have a report that is struggling for performance, the last thing you want is to be creating several extra measures.
Microsoft should fix this.
Doh! Your solution was so simplistic that I totally missed that option. That has saved me a lot of recoding and hair pulling. Thank you!
Click here to learn more about the August 2022 updates!
Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.