topic Re: Why is my filter direction getting ignored? in DAX Commands and Tips

Why is my filter direction getting ignored?

Kurren — Wed, 29 Mar 2023 15:48:23 GMT

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?

Re: Why is my filter direction getting ignored?

johnt75 — Wed, 29 Mar 2023 15:45:54 GMT

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.

Re: Why is my filter direction getting ignored?

Kurren — Thu, 30 Mar 2023 08:22:11 GMT

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) )

Re: Why is my filter direction getting ignored?

JohnShepherdAPD — Thu, 30 Mar 2023 08:35:09 GMT

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 )

Re: Why is my filter direction getting ignored?

johnt75 — Thu, 30 Mar 2023 08:44:07 GMT

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.

Re: Why is my filter direction getting ignored?

tamerj1 — Thu, 30 Mar 2023 09:35:12 GMT

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.