Forum Discussion
Two different behaviour with similar measure
The best way to learn about CALCULATE and FILTER is to play and experiment ...
For example use this test data
EmployeeGradeSalary
| Employee | Grade | Salary |
| Peter | 1 | 1000 |
| Mary | 2 | 2220 |
| Sue | 3 | 3450 |
| Jane | 4 | 1560 |
| Henry | 1 | 4890 |
| Gill | 2 | 5360 |
| Oscar | 3 | 7545 |
| Sheila | 4 | 8000 |
| Paul | 5 | 7800 |
and use this example output
Look at my exampel PBIX
The FILTER command creates a temporay table.
For example
Staff on 2 to 4 =
var tempfile =
FILTER(
yourdata,
yourdata[Grade] >= 2 &&
yourdata[Grade] <= 4)
RETURN
COUNTROWS(tempfile)
The SUM command default to the natural context
Salaries =
SUM(yourdata[Salary])
The CALCULATE command can overide the natural context with the FILTER tempfile.
This can be done with or without VAR.
I prefer with a VAR because it is easier to learn, document,test, and debug.
Filter1 =
var tempfile =
FILTER(
yourdata,
yourdata[Grade] >= 2 &&
yourdata[Grade] <= 4)
RETURN
CALCULATE(
SUM(yourdata[Salary]),
tempfile
)
Filter2 =
CALCULATE(
SUM(yourdata[Salary]),
FILTER(
yourdata,
yourdata[Grade] >= 2 &&
yourdata[Grade] <= 4))
However, we can code the CALCULATE without the FILTER and get the same results
because DAX automically applies the FILTER to the CALCULATE in this scenario
Calculate 1 =
CALCULATE(
SUM(yourdata[Salary]),
yourdata[Grade] IN {2,3,4}
)
Calculate 2 =
CALCULATE(
SUM(yourdata[Salary]),
yourdata[Grade] >= 2 &&
yourdata[Grade] <= 4
)
Learn more about CALCUALTE and FILTER here
https://learn.microsoft.com/en-us/dax/calculate-function-dax
https://learn.microsoft.com/en-us/dax/filter-functions-dax#
https://www.youtube.com/watch?v=SOTQ3MiTXT4
https://www.youtube.com/watch?v=-oDpOfhgmzA
Please clcik the thumbs up for all this useful infomation
and click accept solution if you are now wisee about CALCULATE and FILTER,
Thank you