Forum Discussion
Interaction Between FILTER() and ALL()
Hi tomerfaith,
I have made some test based on your description. Please refer:
My sample ORDERS table is like below, my calendar table starts from 2017-06-01 to 2018-02-28 and I have created relationship on Supply Date and Calendar date.
With your provided measure, it works fine and gives me the right result. Please be noticed on the brackets in your expression.
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
)
)
However there exists an issue in your expression. Why are you using ALL() function? You should know that ALL() function will ignore any filters that might have been applied. Without ALL() function, it returns the same result in my sample.
Since I don't know your source table or data. Thereby I suppose your issue might relate to the ALL() function. And it will be more helpful if you can share us your source table structure and some sample data. So that I can know the right direction and make some proper tests rather than just guessing.
Thanks,
Xi Jin.
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.
Hi 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?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.