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
cliveb2016
New Member

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
Zubair_Muhammad
Community Champion
Community Champion

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] ) )

Regards
Zubair

Please try my custom visuals

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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/
Zubair_Muhammad
Community Champion
Community Champion

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] ) )

Regards
Zubair

Please try my custom visuals

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,

@cliveb2016

 

HI,

 

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


Regards
Zubair

Please try my custom visuals

Hi Zubair excellent help.

 

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

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.