Using CALCULATE with filters in a measure (beginners question)

[Andrew-HLP]

Hello.  I have a question re. filters used in a measure.  I have the following set-up.

If I create a slicer directly connected to  'GL Data'[Account] table/column (and I select account 20001 for example) I can override the filter in the calculate command (this works as exactly expected on any column I apply this pronciple to):

'Test 1' = calculate (

sum('GL Data'[Value]),

'GL Data'[Scenario] = "Actual",

'GL Data','GL Data'[Account] = "10001")

However, if I apply a filter to a related table - whether that table is directly related ('Fiscal Calendar') or indirectly related ('Selector CalMth' or Selector 'CalYear') - I have to remove or override the filter on the related table rather than overriding it on 'GLData' for my measure to return the correct result.

Overrides filter and returns expected result:

'Test 2' = calculate (

sum('GL Data'[Value]),

'GL Data'[Scenario] = "Actual",

'Selector CalYear'[CalYear] = 2024  )

Does not override filter (I am using a sample date) and returns incorrect result:

'Test 3' = calculate (

sum('GL Data'[Value]),

'GL Data'[Scenario] = "Actual",

'GL Data'[Date] = Date(2024,03,31)

Is this how you would expect the filter command to be behave?  i.e. I need to override filter at the place it was applied and cannot override on the effects on a related table?

[bhanu_gautam · Accepted solution]

@Andrew-HLP 

Yes, the behavior you are observing is expected when using the CALCULATE function with filters in DAX. The CALCULATE function modifies the filter context for the calculation of the expression inside it. Here are some key points to understand:

Direct Filters on the Same Table: When you apply a filter directly on the same table (e.g., 'GL Data'[Account]), CALCULATE can override this filter directly within the same table. This is why your measure 'Test 1' works as expected:
dax
'Test 1' = CALCULATE(
SUM('GL Data'[Value]),
'GL Data'[Scenario] = "Actual",
'GL Data'[Account] = "10001"
)
Filters on Related Tables: When you apply a filter on a related table, the filter context is propagated through the relationships. To override these filters, you need to address the filter context on the related table itself. This is why your measure 'Test 2' works:
dax
'Test 2' = CALCULATE(
SUM('GL Data'[Value]),
'GL Data'[Scenario] = "Actual",
'Selector CalYear'[CalYear] = 2024
)
Filters on Columns with Relationships: If you try to override a filter on a column that is part of a relationship (e.g., 'GL Data'[Date]), you need to ensure that the filter context is correctly managed. In your measure 'Test 3', you are trying to filter on 'GL Data'[Date], but if there is a filter applied on a related table (like 'Fiscal Calendar'), you need to override the filter on that related table instead:
dax
'Test 3' = CALCULATE(
SUM('GL Data'[Value]),
'GL Data'[Scenario] = "Actual",
'Fiscal Calendar'[Date] = DATE(2024, 3, 31)
)
In summary, to override filters effectively in CALCULATE, you need to consider where the filters are being applied and address them in the appropriate table. If the filter is applied on a related table, you need to override it on that related table rather than on the main table.

[Andrew-HLP]

Thank you - that is really helpful 🙂