CALCULATE is the strongest formula in DAX. The filter arguments of CALCULATE can override any existing filter on the same column. For example, the following measure will always return the revenue for the manager Anil irrespective of filters applied by the table visual on the Manager’s column:
1. Revenue = SUMX(Orders,Orders[Unit Price] * Orders[Order Quantity])
1. Revenue Anil =
2. CALCULATE(
3. [Revenue],
4. Customer[Manager] = "Anil"
5. )
DAX translates the above measure as following:
1. Revenue Anil =
2. CALCULATE(
3. [Revenue],
4. FILTER(
5. ALL(Customer[Manager]),
6. Customer[Manager] = "Anil"
7. )
8. )It is essentially overriding any external filters applied by the visuals and using its filter (Manager = “Anil”).
Introducing KEEPFILTERS
If we want this measure to show revenue only when the Manager’s name is Anil in the filter context and blanks for the rest of the managers, then we wrap around the filter argument in the above measure in KEEPFILTERS.
1. Revenue Anil KEEPFILTER =
2. CALCULATE(
3. [Revenue],
4. KEEPFILTERS(Customer[Manager] = "Anil")
5. )Result:
As the name suggests, KEEPFILTERS keeps the existing filter and adds the new filter to the context. It is combining the filters applied by the table visual, and then it further adds the filter of the Manager’s name equals to Anil.
As a result, it returns like an AND condition where it returns a value when it meets both the criteria, otherwise returns blank.
The following measures will help in a better understanding of the KEEPFILTERS:
1. Revenue Anil-Rob KEEPFILTERS =
2. CALCULATE(
3. [Revenue],
4. KEEPFILTERS(Customer[Manager] in {"Anil", "Rob"})
5. )Output:
It is returning results where both the filter conditions are matching (visual & measure).
It is different from the following measure:
1. Revenue Anil-Rob =
2. CALCULATE(
3. [Revenue],
4. Customer[Manager] in {"Anil", "Rob"}
5. )
The above measure is essentially translated to:
1. Revenue Anil-Rob =
2. CALCULATE(
3. [Revenue],
4. FILTER(
5. ALL(Customer[Manager]) ,
6. Customer[Manager] = "Anil" || Customer[Manager] = "Rob"
7. )
8. )
Use Case KEEPFILTERS
Let us consider creating a measure that returns the revenue of transactions where revenue is greater than 5000. As we do not have any column for revenue in our model, we will use the FILTER function to achieve the objective.
1. Revenue (Large Orders) =
2. CALCULATE (
3. [Revenue],
4. FILTER (
5. ALL(Orders[Unit Price],Orders[Order Quantity]),
6. [Revenue] > 5000
7. )
8. )
The above measure will filter the Order table where the Revenue (Unit Price * Order Quantity) is > 5000, and then return the revenue of the filtered table.
Following is the output:
So far, so good.
The challenge comes when we filter the above visual on Order Quantity:
The output for Revenue (Large Orders) measure remains unchanged, even if we have selected the Order Quantity between 1 & 10. The ALL statement in the measure is overriding any external filters used on the visual:
We can re-write the above measure, and we apply the filter on the entire fact table:
1. Revenue (Large Orders) 2 =
2. CALCULATE (
3. [Revenue],
4. FILTER (
5. Orders,
6. [Revenue] > 5000
7. )
8. )It may look straightforward and give the desired result, but it can have some severe implications on the performance.
The Orders table could be huge, and scanning it row by row to check the condition (Revenue > 5000) can be a time-consuming operation.
KEEPFILTERS to the rescue
We can wrap around our first measure with KEEPFILTERS, which avoids the overwrite of existing filters, and consumes less memory
1. Revenue (Large Orders) KEEPFILTER =
2. CALCULATE(
3. [Revenue],
4. KEEPFILTERS(
5. FILTER(
6. ALL(Orders[Order Quantity],Orders[Unit Price]),
7. [Revenue] > 5000
8. )
9. )
10.)
KEEPFILTERS returns a much smaller table for the iteration as compared to the entire Sales table.
Download sample file