Forum Discussion

tomerfaith's avatar
tomerfaith
Frequent Visitor
8 years ago

Interaction Between FILTER() and ALL()

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.

Need Help

10 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    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's avatar
      tomerfaith
      Frequent Visitor
      8 years ago

      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's avatar
        v-xjiin-msft
        Solution Sage
        8 years ago

        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.