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

What does CALCULATE() do when you leave the filter arguments blank?

I am trying to understand the CALCUALTE function. I have a pretty good idea of why it is useful and even some cases of when to use it,

 

However I understand that you do not need to provide a filter arguement in the CALCULATE function:

 

So you can write something like

 

CALCULATE(X) where X is some expression.

 

Now to me CALCULATE is used to modify filter contexts on where an expression is evaluated, so if you wanted to look at sales of bikes in China you could well write something like

 

CALCULATE(SUM('Sales'[SaleAmount],'Sales'[Type] = "Bike", 'Sales'[Country] = "China")

 

But I am failing to see why calculate is useful without giving any filters.

 

Clearly I am missing something here.

 

Can anyone explain? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: What does CALCULATE() do when you leave the filter arguments blank?

Hi @cliveb2016

 

 

Calculate without filter argument does a very important job

 

"It transforms existing row context into a filter context"

 

For example inside a calculated column, see this post

 

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

 

And even in a MEASURE where for example ROW context is introduced by an ITERATOR

Thus the MEASURES below would normally give different results

 

Measure =
SUMX ( Table, CALCULATE ( SUM ( Table1[Column] ) ) )
Measure =
SUMX ( Table, SUM ( Table1[Column] ) )
Try my new Power BI game Cross the River

View solution in original post

5 REPLIES 5
Super User
Super User

Re: What does CALCULATE() do when you leave the filter arguments blank?

Hi @cliveb2016

 

 

Calculate without filter argument does a very important job

 

"It transforms existing row context into a filter context"

 

For example inside a calculated column, see this post

 

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

 

And even in a MEASURE where for example ROW context is introduced by an ITERATOR

Thus the MEASURES below would normally give different results

 

Measure =
SUMX ( Table, CALCULATE ( SUM ( Table1[Column] ) ) )
Measure =
SUMX ( Table, SUM ( Table1[Column] ) )
Try my new Power BI game Cross the River

View solution in original post

cliveb2016 Frequent Visitor
Frequent Visitor

Re: What does CALCULATE() do when you leave the filter arguments blank?

Thanks for the answer I understand now.

 

One follow up question:

 

If you add the filter arguments does that change the fact that calculate propagates the row context to filter context?

 

Thanks,

Super User
Super User

Re: What does CALCULATE() do when you leave the filter arguments blank?

@cliveb2016

 

HI,

 

Even if you add the filter arguments.....it won't affect the transformation of RowContext to FilterContext

Try my new Power BI game Cross the River
cliveb2016 Frequent Visitor
Frequent Visitor

Re: What does CALCULATE() do when you leave the filter arguments blank?

Hi Zubair excellent help.

 

Would you mind responding to my related (but new) question. Thanks.

Super User
Super User

Re: What does CALCULATE() do when you leave the filter arguments blank?

Hi,

 

There are generally two triggers to use the FILTER() function:

 

  1. When using rish filters i.e. any comparison other then "column to a fixed value".  Therefore a FILTER() function is to be used when comparing measure with measure, measure with fixed value, column with measure or column with column; and
  2. When you want to resolve a conflict i.e. when there is a conflict between fields that you have dragged to the visual and a condition that you have passed to the CALCULAte() function.  If you want to give precedence to the fields dragged to the visual, then the FILTER() function has to be used.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

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 Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

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: 339 members 3,278 guests
Please welcome our newest community members: