CODEWITHSUNDEEP
powerbidaxdaxstudio
coder_bg
coder_bg

Reputation: 370

Replace BLANKS with 0 using DAX in PowerBI

I have written the following DAX Query

Global Spends = CALCULATE(SUM(PSL[Total Spend]),FILTER(PSL,PSL[PSL Flag] = "Global"))

In some combinations of Global Lead and Class the Global Spends value is BLANK which means that there is no PSL Flag as GLOBAL for that combination. I wanted to replace these blanks with 0 and I wrote this for it :

Global Spends = IF(ISBLANK(CALCULATE(SUM(PSL[Total Spend]),FILTER(PSL,PSL[PSL Flag] = "Global"))),0,CALCULATE(SUM(PSL[Total Spend]),FILTER(PSL,PSL[PSL Flag] = "Global"))

The following measure works with 0 errors but my table in power BI gives me more extra rows for all the combinations where Global Spends are 0 and hence disturbing the whole table.

How Do I replace blanks with 0's?

Sample Table

Global Lead | Class |Global Spends | Local Spends | Total Spends 
Marie | Software | 3M | 5M | 8M
Leela | Hardware |  | 2M | 2M

Here I want the blank to replace with 0.

Upvotes: 3

Views: 1087

Answers (2)

Nicolas Rousseau
Nicolas Rousseau

Reputation: 1

You should try this :

Global Spends = 
var ct = COUNT('PSL'[Lead]) 
return IF(ct > 0, CALCULATE([Total Spends], KEEPFILTERS('PSL'[PSL Flag] = "Global")) +0, BLANK())

Upvotes: 0

Marcus
Marcus

Reputation: 4015

For this particular visual:

Global Spends :=
IF ( 
    SUM ( PSL[Total Spend] ) > 0 ,
    CALCULATE (
        SUM ( PSL[Total Spend] ) ,
        PSL[PSL Flag] = "Global"
    ) + 0 
)

Upvotes: 1

Related Questions