Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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:

Source table:

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

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

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    Anonymous 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]) )
)
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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