Search code examples
powerbidaxdaxstudio

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.


Solution

  • For this particular visual:

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