Confused by CALCULATE and when to use FILTER

DAX DAX Calculations
1

Hi,

Sample pbix: CALCULATE query.pbix (53.4 KB)

I have a very simple table ‘Inventory’, which has ‘Inventory Types’ of either ‘Stock’ or ‘Sold’.

Today I created two measures which I expected to simply provide a count of the number of rows from the table where the Inventory Type was “Stock” or “Sold”. My measures were:

Stock Units = CALCULATE(COUNTROWS(Inventory), Inventory[Inventory Type] = "Stock")

Sold Units = CALCULATE(COUNTROWS(Inventory), Inventory[Inventory Type] = "Sold")

I then created a matrix and dropped in Inventory Type and the 2 measures, but to my surprise the same values were repeated for both rows:

1

Whereas I was only expecting to see a value for Stock Units on row 2 and a value for Sold units on row 1, like the below:

2

It was only when I included a FILTER in my measures, did the values show as expected:

Stock Units with Filter =
CALCULATE (
    COUNTROWS ( Inventory ),
    FILTER ( Inventory, Inventory[Inventory Type] = "Stock" )
)

Sold Units with Filter =
CALCULATE (
    COUNTROWS ( Inventory ),
    FILTER ( Inventory, Inventory[Inventory Type] = "Sold" )
)

image

I have always been of the understanding, that when you write a CALCULATE statement, that behind the scenes, a FILTER will be wrapped around the filter which is provided - taken from Dax Guide - Calculate:

image

So why is it thay my two measures, one with FILTER and the other without are behaving different?

One thing I have just noticed is that if I use my original measures on another field which is not Inventory Type, that they do appear as expected, for example by Brand:

image

I have created hundreds of basic Dax measures using CALCULATE in the last year without ever having to include FILTER within them. This has stumped me. Each time I think I am getting to grips with DAX, something comes along and proves that is 100%, definitely not the case!

Many thanks in advance.

Mark

2

@Mark,

For what seems like a simple function, what goes on behind the scenes with ALL() definitely can be confusing.

The answer to your question lies in the highlighted line of your DAX Guide snapshot:

image
image.png692×452 34.8 KB

If you revise your measure to reflect what is going on behind the “syntax sugar” of the CALCULATE statement so that you’re comparing apples to apples, you will get the same results:

image

image
image.png874×119 4.73 KB

The ALL() around the Inventory Type column tells it to ignore the context of inventory type in your matrix.

This also explains why you got the results you expected when you used Brand to define your rows in the matrix visual, since the implied ALL() statement did not apply to the Brand column, only to Inventory Type.

Hope this helps clarify the issue.

  • Brian
3

Hi @BrianJ

Aha - yes I’ve got it now, thanks. I feel somewhat satisfied that I had at least exposed part of the solution in my investigations - I just needed someone to spell it out!

So, as a rule of thumb, would you always advise including a FILTER when creating such a measure- just in case it is going to be reported alongside the dimension it is filtering on?

Cheers

Mark

4

@Mark,

I sometimes get lazy and use the simple CALCULATE syntax, but personally I do think it’s a best practice to spell out what you’re doing as clearly as possible by including an explicit FILTER statement.

  • Brian