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
CloudMonkey
Post Prodigy
Post Prodigy

Calculate is escaping the filter context?

Hi,

 

I have 2 test formulae:

 

Cumulative Volume = CALCULATE(sum('Product'[Volume]),filter(all('Product'),
    'Product'[Date] <= max('Product'[Date])
))

 

Total Volume = CALCULATE(sum('Product'[Volume]), 
FILTER(
all('Product'),
    'Product'[Date] <= CALCULATE( max('Product'[Date]) )
)
)

 

Which give the following outputs:

Capture 2057pm.JPG

 

The first formula successfully calculates the cumulative total and the second one returns the grand total. For the second formula please can you tell me why CALCULATE( max('Product'[Date]) ) returns the last date in the date column, rather than the current date? I thought CALCULATE inherits the existing filter context - given that no filters are in this CALCULATE expression please can you tell me why it gives a different result to max('Product'[Date])?

 

Thanks for your help,

 

CM

 

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@CloudMonkey 

 

Calculate function  transforms row context to Filter context.

 

In your first formula the filter context is provided by the Table/Matrix visual you are using.

 

In the second case, CALCULATE transforms the row context of the ALL(Product) table into a FILTER context and is assessed/computed in this sub context.

Check out the SQLBI website. There are lot of articles on this topic

 

https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/

 

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

1 REPLY 1
Zubair_Muhammad
Community Champion
Community Champion

@CloudMonkey 

 

Calculate function  transforms row context to Filter context.

 

In your first formula the filter context is provided by the Table/Matrix visual you are using.

 

In the second case, CALCULATE transforms the row context of the ALL(Product) table into a FILTER context and is assessed/computed in this sub context.

Check out the SQLBI website. There are lot of articles on this topic

 

https://www.sqlbi.com/articles/context-transition-and-filters-in-calculate/

 

 

 


Regards
Zubair

Please try my custom visuals

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.