topic Calculate and filter context in DAX Commands and Tips

Calculate and filter context

Zx2000 — Mon, 30 Jan 2023 13:33:37 GMT

Hello,

I guess this is a simple question but I am going nuts why it doesn't work. So as far I studied DAX book and read your articles I know that if we add an filter as a CALCULTE argument adds a filter to the filter context or replaces it (when it filters same column). So why it does not work in this example - I have a date table with some additional columns like ("Month Year") which are used as rows in matrix:

My measure is:

CALCULATE( [Sales Amount], Daty[Date]= DATE(2023,1,27) )

So outer filter context in this matrix is set for Dates[Month Year] column (in rows). In a measure I use filter for Dates[Date]. So at each row there is a filter Month-Year and Calculate adds Daty[Date]= DATE(2023,1,27). So final filter context uses 2 columns Dates[Date] and Dates[Month Year]. The filter from CALCULTE does not remove any filter here as it uses different column for filtering. So why I am getting same value in every row? As an example the row with filter February 2023 AND DATE(2023,1,27) should be empty but it gives Sales from 27.1.2023. So there should be result only in January. If a use any other column from Dates table in CALCULTE argument it works like it should.

Re: Calculate and filter context

ValtteriN — Mon, 30 Jan 2023 13:49:35 GMT

Hi,

Your measure returns always the same value (the sales of 27.1.2023). Now what is happening is that you have hard-coded a filter to your measure. So basically your measure = "calculate sales from fact table, but only consider the values on date 27.1.2023. Since your visual uses values from your calendar table an you likely have 1:m relationship between your fact and calendar table, from your visuals perspective every month will get the same value. E.g on February the measure will still get the same value since it considers the 27.1.2023 date. However if you were to use data from your fact table your visual would make sense:

Measure 38 = CALCULATE(SUM('Table (22)'[Column1]),'Calendar'[Date]=DATE(2023,1,1))

Using column from calendar:

using column from fact:

Only January gets values so only it is displayed.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Calculate and filter context

Zx2000 — Mon, 30 Jan 2023 15:48:57 GMT

Actually I don't understand it. What means I hard coded it? These rules for CALCULATE works in every other table in a model.

Re: Calculate and filter context

FreemanZ — Tue, 31 Jan 2023 09:00:13 GMT

hi Zx2000

It is because a filter arugment inside a CALCULATE can overwrite outer fitlering context of a measure. In case of conflict, the filter argument prevails.

Re: Calculate and filter context

Zx2000 — Thu, 02 Feb 2023 16:02:15 GMT

So I found the solution. The solution is off course crazy becasue there is no clear information about this almost nowhere. Seems that DAX is real hell. So...as far as I understand:
If someone uses calculate with [date] column and:

-[date] column is a connector to other tables OR

-if table is "marked as date table" (when a date table is connected with other table but by other column ie. DateKey)

=> the ALL('date table") is added to the formula. This happend in the background (and of course almost no one knows it and you will not see it). Congrats for DAX developers.

Re: Calculate and filter context

Zx2000 — Thu, 02 Feb 2023 15:54:13 GMT

This is wrong.

Re: Calculate and filter context

Zx2000 — Thu, 02 Feb 2023 15:59:14 GMT

Nope. It is wrong. They overwrite filter when filtering the same column. There is no confilct here. There is a outer filter context (year - month) and new context (single date). In normal case it should be combined.