cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CloudMonkey Member
Member

Filter context using MAX

Hi,

 

I'm using this pbix file https://ufile.io/8gy9deoz and I'm using the following equation to find the cumulative total. I'm confused because max('Product'[Date]) appears to find the maximum Product[Date] in the current filter context (i.e. the date of the axis of the visualisation), whereas I thought ALL('Product') clears all filters (including the visualisation's axis?). Please can someone help me understand why MAX seems to be respecting the filter context of the visualisation axis despite ALL being used?

 

 

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

 

Output visualisation:

cumulative.JPG

Source table:

backend.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter context using MAX

@CloudMonkey I guess this will answer your question if you change your measure as below.

 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





2 REPLIES 2
Super User
Super User

Re: Filter context using MAX

@CloudMonkey I guess this will answer your question if you change your measure as below.

 

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





Did I answer your question? Mark my post as a solution.

Proud to be a Datanaut! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





CloudMonkey Member
Member

Re: Filter context using MAX

Thanks @parry2k I think the penny's dropped. So ALL doesn't change the filter context of the whole of the calculate statement, just for the table expression used for iteration (not sure if iteration is the correct word!)

 

Thanks,

 

CM

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 375 members 3,753 guests
Please welcome our newest community members: