Interaction Between FILTER() and ALL()

[tomerfaith]

First let me describe the situation:
I have a table with "Order Date" , "Supply Date" and "Sum" columns, and a date table.
I created a relationship between "Supply Date" and the date table. and i need this relationship for other parts of my report.

Now I want to display the open orders of every month and keep filters like: customer, part ,status etc..

I tried few methods using FILTER() and ALL():

1)

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

This had no effect at all on the FILTER() function (the orders table was still filterd via the relationship I mentioned).
so i tried:

2)

Order Sum Open At The Time = VAR minDate = MIN('Calender'[Date]) RETURN
                            VAR maxDate = MAX('Calender'[Date]) RETURN 
                            CALCULATE(SUM('ORDERS'[Sum]),
                                FILTER(CALCULATETABLE('ORDERS',ALL('ORDERS'[Suply Date])),
                                     'ORDERS'[Order Date])<=maxDate && 'ORDERS'[Supply Date]>=minDate
                                      )
                                    ) 

again, this had no effect at all.

The last thing was to use ALLEXCEPT():
3)

Order Sum Open At The Time = VAR minDate = MIN('Calender'[Date]) RETURN
                            VAR maxDate = MAX('Calender'[Date]) RETURN 
                            CALCULATE(SUM('ORDERS'[Sum]),
                                FILTER( FILTER(ALLEXCEPT('ORDERITEMS',..,...//here there is a long list of every column in my report except "Supply Date"),
                                 ,
                                     'ORDERS'[Order Date])<=maxDate && 'ORDERS'[Supply Date]>=minDate
                                      )
                                    ) 
                               

This did work but requires me to type a list of all columns in my report except "Supply Date", this is not very good since my report is very big.

So my question is how can I use filter while keeping filters on all columns except of one column (in this case supply date)?
Please help me figure this out.

[v-xjiin-msft]

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.

[tomerfaith]

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. 

[v-xjiin-msft]

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.

[tomerfaith]

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.

[tomerfaith]

Sadly I still get similar result:

[v-xjiin-msft]

Hi @tomerfaith,

Could you please share us the pbix file with OneDrive or something else? So that I can dig deeper.

Thanks,
Xi Jin.

[tomerfaith]

I have created  a test pbix:
https://drive.google.com/file/d/1LaPNFfoSuxURiIQfJ-4L5aUEiw-OkRMs/view?usp=sharing

[v-xjiin-msft]

Hi @tomerfaith,

I found out the issue. In my sample, the relationship between Calendar and ORD is one to one. However in yours, it is many to one.

To resolve your issue, you just need to change the relationship to one to one.

And if you want to know why. Honestly I have no idea. It seems like there exists some limitations which we don't know in many to one relationship.

Thanks,
Xi Jin.

[tomerfaith]

First thank for helping.
I have 2 problems:

1)Even when I set the relatonship to one to one I still get this:

while the 60 total is correct, the table does not show all the orders which were open during 2017.

2) In the real report the ORD table is big and the relationship IS many to one (power bi won't let me change this),