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

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

Sadly I still get similar result:

 

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

    Hi tomerfaith,

     

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

     

    Thanks,
    Xi Jin.

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

        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.