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:
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:
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" ) )
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:
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:
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.