Why is my filter direction getting ignored?

[Kurren]

You can see a simple example of this problem here, a dashboard with only 2 tables.

I have an invoices and a dates table, many invoices to one date. The filter direction is from dates to invoices, like this:

When I evaluate the following:

CALCULATE ( MAX ( Dates[Date] ), Invoices[ID] = "0,92270" )

I get back the maximum date in the date table: 31/12/2022. 

When I evaulate this:

VAR FilteredInvoices = FILTER ( Invoices, Invoices[ID] = "0,92270" )
RETURN
  CALCULATE ( MAX ( Dates[Date] ), FilteredInvoices )

I get back the invoice date for the invoice I filtered; the Dates table is being filtered.

Why is the date table being filtered in the second query but not in the first, I thought the two statements were equivalent? Also how can I remove the filter on the dates table in the second statement?

[tamerj1 · Accepted solution]

Hi @Kurren 
You need to read about expanded tables Expanded tables in DAX - SQLBI. And yes, if you place the fact table as CALCULATE filter argument it will filter the Dimension table. This is actually a common patter often use to calculate values based on dimension tables. The reason is that the columns of the Customer table are part of the expanded Orders table. I have put the last example in a separate screenshot to clarify the idea.

[JohnShepherdAPD]

Table variables are immutable, meaning the filter has to be applied at the variable stage try:

 VAR FilteredOrders =   
        FILTER ( Orders, Orders[ID] = 2 ,REMOVEFILTERS( Customers ))
    RETURN
        CALCULATE ( MAX ( Customers[Credit Limit] ), FilteredOrders )

[johnt75]

When you filter a table, as opposed to a column, the entire expanded table gets added into the filter context, and that includes tables at the one-side of a relationship from the filtered table, so the entire date table is being added to the filter context in addition to the invoices table.

You could add REMOVEFILTERS('Date') to the CALCULATE in the second snippet if you needed to.

[Kurren]

Thanks @johnt75. Your suggestion unfortunately does not work. If we use the minimal example here with this schema:

The following does not affect the result:

    VAR FilteredOrders =   
        FILTER ( Orders, Orders[ID] = 2 )
    RETURN
        CALCULATE ( MAX ( Customers[Credit Limit] ), FilteredOrders, REMOVEFILTERS(Customers) )

[johnt75]

You're right, because both the filter table and REMOVEFILTERS are at the same level they are not interacting. You could use the following convoluted code

VAR FilteredOrders = FILTER( 'Orders', 'Orders'[ID] = 2 )
RETURN
	CALCULATE(
		MAXX(
			ALL( 'Customers'[Credit Limit] ),
			'Customers'[Credit Limit]
		),
		FilteredOrders
	)

but it is obviously better to use the code from the first snippet in your original post.