Forum Discussion
Interaction Between FILTER() and ALL()
I created a table similar to yours:
and used this code:
Order Sum Open At The Time = VAR minDate = MIN('Calender'[Date]) RETURN
VAR maxDate = MAX('Calender'[Date]) RETURN
CALCULATE(SUM('ORDERS'[Sum]),ALL('ORDERS'[Supply Date]),
FILTER('ORDERS',
'ORDERS'[Order Date]<=maxDate && 'ORDERS'[Supply Date]>=minDate
)
)
Now when I filter to show 2017, I see:
This is not working as intended becouse it is showing only rows where the supply date was 2017 (becouse of the relationship), although there are other rows that answer the criteria.
Thats why I need some implementation of ALL() to remove the filter from the table.
In your table, all rows are ordered and shipped in the same year and thats why it would seem that its working.
Solution SageHi tomerfaith,
Yes, using ALL() is a right method. But you should put ALL() function into Filter(). Modify your expression like this:
Order Sum Open At The Time 2 = VAR minDate = MIN('Calender'[Date]) RETURN
VAR maxDate = MAX('Calender'[Date]) RETURN
CALCULATE(SUM('ORD'[Sum]),//ALL('ORD'[Supply Date]),
FILTER(ALL('ORD'),
'ORD'[Order Date]<=maxDate && 'ORD'[Supply Date]>=minDate
)
)
Thanks,
Xi Jin.
Yes, the problem is that when I use the implemantation you suggested:
Order Sum Open At The Time 2 = VAR minDate = MIN('Calender'[Date]) RETURN VAR maxDate = MAX('Calender'[Date]) RETURN CALCULATE(SUM('ORD'[Sum]),//ALL('ORD'[Supply Date]), FILTER(ALL('ORD'), 'ORD'[Order Date]<=maxDate && 'ORD'[Supply Date]>=minDate ) )I lose all the filters in my report.
when I actually only want to lose the filters specificly on "Supply Date" column.
my question is how can I do this?- v-xjiin-msft
Hi tomerfaith,
Check this:
Order Sum Open At The Time 2 = VAR minDate = MIN('Calender'[Date]) RETURN VAR maxDate = MAX('Calender'[Date]) RETURN CALCULATE(SUM('ORD'[Sum]),//ALL('ORD'[Supply Date]), FILTER(ALL('ORD'[Supply Date],ORD[Order Date]), 'ORD'[Order Date]<=maxDate && 'ORD'[Supply Date]>=minDate ) )Thanks,
Xi Jin.Sadly I still get similar result: