cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cliveb2016 Frequent Visitor
Frequent Visitor

Why does CALCULATE behave like this when using FILTER?

Hi I have a question related to the tables discussed here:

 

http://www.excelnaccess.com/context-transition-using-calculate/

 

Why does the calculated column:

 

Column = CALCULATE(SUM(Table1[earnings]),Table1[Year] = 2011)

Behave differently to

 

Column = CALCULATE(SUM(Table1[earnings]),FILTER(Table1, Table1[Year] = 2011))

 

The first returns the total for each one correctly whereas the second returns the total for all athletes for the year 2011 rather than the athlete in that row.

 

I guess for some reason the use of 

 

FILTER

 

Has some how gotten rid of the row context in the calculated column but I thought that CALCULATE would convert the row context into the FILTER context and give back the same result???

 

Can anyone explain?

 

Bonus question: Do calculated columns induce a row context (i.e. when you use a calculated column does it evaluate the column formula for each row? If so why do we get the behaviour as shown in measure 2?)

 

Thank you so much!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Why does CALCULATE behave like this when using FILTER?

@cliveb2016

 

This is a lovely question

My article only covers the basics

 

Also see what happens with

 

Column = CALCULATE(SUM(Table1[earnings]),Table1)

 

Actually it has to do with the order in which arguments are evalauted.
Filter Parameter of the Calculate is exected first

Please see this  article

 

https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/

 

So when you introduce Filter(Table1), it introduces an UnFiltered Table in a Calculated Column (ROW CONTEXT). So it has the impact of removing the FILTERS

When you use

Column = CALCULATE(SUM(Table1[earnings]),Table1[Year] = 2011


This is internally transformed by DAX Engine into following formula'

See the article https://www.sqlbi.com/articles/filter-arguments-in-calculate/

 

Column = CALCULATE(SUM(Table1[earnings]),Filter(all(Table1[Year]),Table1[Year] = 2011))

So this formula retains all other filters except for Table1 Year which you modify to be 2011

 

Now if you want your second formula to give same results, you would have to use

 

Column = CALCULATE(SUM(Table1[earnings]),FILTER(RelatedTable(Table1), Table1[Year] = 2011))

 

View solution in original post

1 REPLY 1
Super User
Super User

Re: Why does CALCULATE behave like this when using FILTER?

@cliveb2016

 

This is a lovely question

My article only covers the basics

 

Also see what happens with

 

Column = CALCULATE(SUM(Table1[earnings]),Table1)

 

Actually it has to do with the order in which arguments are evalauted.
Filter Parameter of the Calculate is exected first

Please see this  article

 

https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/

 

So when you introduce Filter(Table1), it introduces an UnFiltered Table in a Calculated Column (ROW CONTEXT). So it has the impact of removing the FILTERS

When you use

Column = CALCULATE(SUM(Table1[earnings]),Table1[Year] = 2011


This is internally transformed by DAX Engine into following formula'

See the article https://www.sqlbi.com/articles/filter-arguments-in-calculate/

 

Column = CALCULATE(SUM(Table1[earnings]),Filter(all(Table1[Year]),Table1[Year] = 2011))

So this formula retains all other filters except for Table1 Year which you modify to be 2011

 

Now if you want your second formula to give same results, you would have to use

 

Column = CALCULATE(SUM(Table1[earnings]),FILTER(RelatedTable(Table1), Table1[Year] = 2011))

 

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 365 members 3,427 guests
Please welcome our newest community members: