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
shhafiz
Employee
Employee

Explanation of Context in Calculating Cumulative Values

There are several posts answering this, but I am not clear on the concept used to get this:

 

 

Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED('BI-Dates'[Date]),
        'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date] )
    )

 

 

The abobe is quoted from an answer post. I understnd the FILTER is used to get the rows for which we want to calculate the sum.,

The ALLSELECTED in filter means we are trying to remove any explcit filter based on Date column, thereby selecting all column.

Now, the next paramter of FILTER is where I am confused: 'BI-Dates'[Date] <= MAX ( 'BI-Dates'[Date])

 

This is supposed to select dates which are less than or equal to Date in current context, that is in a bar chart of Cumulative Sales vs Date, for any Date on the axis, it will include all Sales on or before that date. Now if we remove filtering on Date through ALLSELECTED, shouldn't MAX () return the very last day irrespective of our context date as we move along the axis? That is, the cumulative values will be grand cumulative value for all dates and all our values will be same.

If parameter to Max is the current context Date, is there any point in using Max as it will be the last date for which we are trying to calculate a cumulative? Also, the logical condition has the same Date column on sides of the operator, why will the left one represent all Dates while the one inside Max will come from our context Date?

 

Sorry if the post is confusing but I am also equally confused.
 

 

 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey,

 

I will give it a try to explain the DAX statement.

 

First, some principles

  • a measure is evaluated in a certain context, to visualize this context (talking about mental visualization) i use the matrix, the context a measure is evaluated is determined by cell in this matrix, but not just by row and column headers but also by slicers
  • the rows that are used (aggregated) by the measure are filtered down by slicers, row headers and column headers, slicers, row headers and column headers define the Filter Context that determines the rows (the Filter Context filters down the rows of the table) used by the expression, eg. SUM('table'[numeric value])

The DAX function CALCULATE(... , ... , ...) allows to alter the Filter Context and thus the rows used by the expression, the first parameter of CALCULATE.

 

Some kind of rule that has to be considered, before the expression is evaluated, the other parameters 2 to n are applied and thereby altering the current Filter Context of the cell.

 

The function FILTER(...) returns a table, this table expands or reduces the rows that are aggregated by the expression, this is a good, but also a little mind-boggling read about expanded tables:

https://www.sqlbi.com/articles/context-transition-and-expanded-tables/

 

The first parameter of FILTER(...) is by itself a parameter, for each row of this table the condition evaluated, if the evaluation results to TRUE, the row of the table remains otherwise the row is purged from the initial set of rows. This means that FILTER(...)  returns a table of rows, that successfully passed the condition. This reminds us, that FILTER(...) is an iterator function, this means that it iterates through all the rows of the table or a DAX statement that returns a table,

 

ALLSELECTED(...) returns a table, in this case, a one-columned table. The magic of ALLSELECTED(...) is that it returns not just the Date value of the current row or column, but all the Date values that are used in the current query context. Of course this also works with other columns not just with Date columns.

 

One of the most mind-boggling quests in DAX I had to tackle is the same question that you are asking, what is the left part of the comparison and what is the right part, the MAX('...'[Date]).

 

Knowing that FILTER() is an iterator function, that iterates through each row of that table, it's possible to refer to a column of that table, precisely the value of the specified column of the currently iterated row. For this reason it's valid to state that the part of the comparison w/o an aggregation function represents the current row value of the iterated table.

 

Now the MAX(...) part, the article I mentioned above explains the concept of expanded tables, a much deeper explanation can be found in the book "The Definitive Guide DAX" by @AlbertoFerrari and @marcorusso. I recommend to read this book very carefully, if you want to truly understand the mechanics of DAX.

This book also explains that CALCULATE does not replace columns contributing to the current Filter Context, instead are columns are added. This means suddenly there are two date columns. The right part of the comparison refers to column of the current context. MAX(...) represents the current value, of the current row. Because we have two columns we have to use an Aggregation Function to refer to its value.

 

Hope this helps 

 

Regards

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

Hey,

 

I will give it a try to explain the DAX statement.

 

First, some principles

  • a measure is evaluated in a certain context, to visualize this context (talking about mental visualization) i use the matrix, the context a measure is evaluated is determined by cell in this matrix, but not just by row and column headers but also by slicers
  • the rows that are used (aggregated) by the measure are filtered down by slicers, row headers and column headers, slicers, row headers and column headers define the Filter Context that determines the rows (the Filter Context filters down the rows of the table) used by the expression, eg. SUM('table'[numeric value])

The DAX function CALCULATE(... , ... , ...) allows to alter the Filter Context and thus the rows used by the expression, the first parameter of CALCULATE.

 

Some kind of rule that has to be considered, before the expression is evaluated, the other parameters 2 to n are applied and thereby altering the current Filter Context of the cell.

 

The function FILTER(...) returns a table, this table expands or reduces the rows that are aggregated by the expression, this is a good, but also a little mind-boggling read about expanded tables:

https://www.sqlbi.com/articles/context-transition-and-expanded-tables/

 

The first parameter of FILTER(...) is by itself a parameter, for each row of this table the condition evaluated, if the evaluation results to TRUE, the row of the table remains otherwise the row is purged from the initial set of rows. This means that FILTER(...)  returns a table of rows, that successfully passed the condition. This reminds us, that FILTER(...) is an iterator function, this means that it iterates through all the rows of the table or a DAX statement that returns a table,

 

ALLSELECTED(...) returns a table, in this case, a one-columned table. The magic of ALLSELECTED(...) is that it returns not just the Date value of the current row or column, but all the Date values that are used in the current query context. Of course this also works with other columns not just with Date columns.

 

One of the most mind-boggling quests in DAX I had to tackle is the same question that you are asking, what is the left part of the comparison and what is the right part, the MAX('...'[Date]).

 

Knowing that FILTER() is an iterator function, that iterates through each row of that table, it's possible to refer to a column of that table, precisely the value of the specified column of the currently iterated row. For this reason it's valid to state that the part of the comparison w/o an aggregation function represents the current row value of the iterated table.

 

Now the MAX(...) part, the article I mentioned above explains the concept of expanded tables, a much deeper explanation can be found in the book "The Definitive Guide DAX" by @AlbertoFerrari and @marcorusso. I recommend to read this book very carefully, if you want to truly understand the mechanics of DAX.

This book also explains that CALCULATE does not replace columns contributing to the current Filter Context, instead are columns are added. This means suddenly there are two date columns. The right part of the comparison refers to column of the current context. MAX(...) represents the current value, of the current row. Because we have two columns we have to use an Aggregation Function to refer to its value.

 

Hope this helps 

 

Regards

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens

 

Thanks for the reply, it now makes sense but I now have a follow up question.

 

If we do not use Max on the right side of expression but use the column name, which of the Two Date column will it refer to? By reading more articles, that Date comes from Dates in current context, which should all be same if we are using Date on the axis. Do we even need Max in this case?

Hey,

 

I'm trying to answer your questions by example, I have simple calendar table created by using this DAX statement

Calendar = CALENDAR("2017-01-01", "2017-12-31")

Using a measure simply counting the rows ot the table returned by the FILTER(...) statement

NumberOfDays = 
	COUNTROWS(
		FILTER(
			ALL('Calendar'[Date])
			,'Calendar'[Date] <= 'Calendar'[Date]
		)
	)

returns always 365, even if I use the column 'Calendar'[Date] on rows.

 

The table passed to FILTER as 1st parameter contains all Dates (necessary to calculate the YTD), FILTER iterates through all the rows and checks the conditon, if the condition results to TRUE, FILTER keeps that row otherwise the row is omitted, meaning not contained in the resulting table.

 

What happens in the above statement is the following the value of the current iteration is compared to itself, similar to 1 = 1. Using the 'Calendar'[Date] does not "leave" the Row Context introduced by FILTER(), for this reason, both sides of the comparison are referencing the exact same object - the column from the iterator.

 

So what is the differenct if we use MAX(...), MAX and the other Aggregation functions (like SUM, MIN, ...) aggregate the column values in the current Filter Context, what has to be "deeply" considered is "current Filter Context". Using MAX() means the values of the current Filter Context are aggregated. If the date column is used as slicer, column, or row the usage of MIN or MAX returns the same result, because there is just one value (omitting the question what happens if two dates are selected). But if a column is used on rows like Year or Month (Feb 2017) the usage of MIN will not return the same result as MAX.

 

For this reason, you can't omit the MAX(...) function

But you can use

FILTER(
  ...
  ,MAX('Calendar'[Date] >= 'Calendar'[Date]
)

and you will retrieve the same result as using

FILTER(
  ...
  ,'Calendar'[Date] <= MAX('Calendar'[Date]
)

I always use the latter, because it's a little more intuitive, at least to my understanding 😉

 

Hopefully this explains the missing part.

 

Regards
Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.