# Replace Blank with 0 in Calculate



## potap (Jun 13, 2016)

Hi!

Lets say product E doesn't have any transaction in the Sale table. I want it to be displayed with a 0 in my pivot table so I wrote this measure :


```
=CALCULATE( SUM( factSale[Amount] ) + 0; dimProduct[Group] = "Bike" )
```

This way, product E gets a 0 but the problem is that every product from every other product group now also get a 0. I don't understand this behavior and I am looking for a solution.

Thank you!


----------



## anvg (Jun 14, 2016)

Hi
Try with

```
[COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
    [COLOR=#0070FF]HASONEVALUE[/COLOR][COLOR=#969696] ([/COLOR] dimProduct[Group] [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]IF[/COLOR][COLOR=#969696] ([/COLOR]
        [COLOR=#0070FF]VALUES[/COLOR][COLOR=#969696] ([/COLOR] dimProduct[Group] [COLOR=#969696])[/COLOR] = [COLOR=#D93124]"Bike"[/COLOR];
        [COLOR=#0070FF]CALCULATE[/COLOR][COLOR=#969696] ([/COLOR] [COLOR=#0070FF]SUM[/COLOR][COLOR=#969696] ([/COLOR] factSale[Amount] [COLOR=#969696])[/COLOR] + [COLOR=#EE7F18]0[/COLOR]; dimProduct[Group] = [COLOR=#D93124]"Bike"[/COLOR] [COLOR=#969696])[/COLOR];
        [COLOR=#0070FF]BLANK[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR]
    [COLOR=#969696])[/COLOR];
    [COLOR=#0070FF]BLANK[/COLOR][COLOR=#969696] ([/COLOR][COLOR=#969696])[/COLOR]
[COLOR=#969696])[/COLOR]
```
Regards,


----------



## Dan80 (Jun 14, 2016)

Hi Anvg, interesting stuff again from yourself.  Any chance you can explain the formula, if possible please?  Why do you need "HASONEVALUE", what does it do? Thanks, Dan


----------



## potap (Jun 14, 2016)

anvg said:


> Hi
> Try with
> 
> ```
> ...




Thanks anvg! Your idea got me going 

I thought about the IF(VALUES()) part but I was stuck with the "A table of multiple values was supplied where a single value was expected" error. Adding the HASONEVALUE() part is great. I tweaked it a little bit to get the right total.


```
Total Sales:=SUM( factSale[Amount] )
```


```
Total Bike Sales:=CALCULATE(                  
                                   [Total Sales];
                                   dimProduct[Group] = "Bike"
                                   )
```


```
Total Bike Sales with 0:=IF(                                  
                                  HASONEVALUE( dimProduct[Group] );
                                  IF(
                                     VALUES( dimProduct[Group]) = "Bike";
                                     [Total Bike Sales] + 0;
                                     BLANK()
                                      );
                                 [Total Bike Sales]
                                 )
```

I found this blog post which works too IF(COUNTROWS(VALUES())=1) but I think the HASONEVALUE solution is neater.

I was hoping for a simpler measure like the first one I posted  What I would like to understand is why Calculate works that way when I add the +0


----------



## akice (Jun 14, 2016)

Dan80 said:


> Hi Anvg, interesting stuff again from yourself.  Any chance you can explain the formula, if possible please?  Why do you need "HASONEVALUE", what does it do? Thanks, Dan



The "HASONEVALUE" function call is needed to make sure the dimProduct[Group] column only has one value in the current filter context being evaluated by the inner IF ( VALUES(dimProduct[Group] = "Bike"...) statement.   VALUES( dimProduct[Group] ) returns a single column table with all the different values visible in the current filter context.  If more than one value was allowed the next/inner comparison operation will fail. And if VALUES( dimProduct[Group] ) does return only a single value, according to the rules of Dax for the VALUES function that single column, single row value is converted into a scalar value and can then be compared to another scalar value ("Bike" in this case).   But without the HASONEVALUE to protect the function, you could end up with an error.  Make sense?


----------

