Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Zx2000
Advocate I
Advocate I

Calculate and filter context

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:
Zrzut ekranu_20230130_131932.png

 

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. 

1 ACCEPTED SOLUTION
Zx2000
Advocate I
Advocate I

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. 

View solution in original post

6 REPLIES 6
Zx2000
Advocate I
Advocate I

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. 

FreemanZ
Super User
Super User

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. 

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. 

ValtteriN
Super User
Super User

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:

ValtteriN_1-1675086485516.png

 

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



Using column from calendar:

ValtteriN_2-1675086509719.png

using column from fact:

ValtteriN_3-1675086547956.png

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is wrong.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors