# DAX: Why is difference between CALCULATE function with FILTER function and with filter expression?



## anvg (Apr 4, 2016)

Hi
I need to calculate count of months for spending of each product. Facultatively I need to show  planned spending of each product which is equal for each month, in "month" field of a monthData table it is blank. That is why I include a filter of calculate month count in a measure which is really required in a fact group subtotal and a row total only. I wrote two measures

```
not call filter count:=CALCULATE(DISTINCTCOUNT(MonthData[Month]),NOT(ISBLANK('MonthData'[Month])))
```
and

```
Filter count:=CALCULATE(DISTINCTCOUNT('MonthData'[Month]),FILTER('MonthData',NOT(ISBLANK('MonthData'[Month]))))
```
They works fine in the subtotal and the row total but first has a mistake calculation.
But when I had a test of those measures I seen some difference in a computed result in month pivot cell (for example H11 and I11). "not call filter count" measure is calculated in I11 the subtotal result.
Does anyone explain me why it is happend? Beacuse I do not see difference between "not call filter count" and "Filter count" measures.
I use Excel 2016. I uploaded my example file Zippyshare.com - Plan-Fact.xlsx
Regards,


----------



## Matt Allington (Apr 4, 2016)

The 2 formulas are semantically different. The equivalent to the first formula is the following. 

```
Filter count:=CALCULATE(DISTINCTCOUNT('MonthData'[Month]),FILTER(ALL('MonthData'),NOT(ISBLANK('MonthData'[Month]))))
```

this is why you get different results.


----------



## anvg (Apr 5, 2016)

Hi, Matt!
Thank you very much.
Regards, Andrey


----------



## akice (Apr 18, 2016)

Sorry for nitpick, but Matt Allington response is not quite accurate.  He wrote:



> The 2 formulas are semantically different. The equivalent to the first formula is the following.
> Code:
> 
> ```
> ...



The Dax engine actually rewrites the first boolean expression formula as:


```
[COLOR=#333333]Filter count:=CALCULATE(DISTINCTCOUNT('MonthData'[Month]),FILTER(ALL('MonthData'[Month]),NOT(ISBLANK('MonthData'[Month]))))[/COLOR]
```

the difference being what is place in the ALL() in the Filter function.  Dax engine only removes the filter on 'MonthData'[Month] column ; it doesn't remove all the filters on 'MonthData'.   The calculations will return equivalent values as long as there are no other filters on 'MonthData' in the current filter context.  If there is another external filter, then results could be different.  I only commented because the distinction could become important when trying determine why results are not as expected.


----------



## anvg (Apr 18, 2016)

Thank you, akice.
I think it was a accidental slip of Matt.


----------



## Matt Allington (Apr 18, 2016)

anvg said:


> Thank you, akice.
> I think it was a accidental slip of Matt.


Yes


----------

