Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
tomerfaith
Frequent Visitor

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.

10 REPLIES 10
v-xjiin-msft
Solution Sage
Solution Sage

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.

 

1.PNG

 

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

 

2.PNG

 

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.

 

3.PNG

 

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: 
table.png

 

 

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:

exemple.png

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

222.PNG

 

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.

Sadly I still get similar result:exemple2.png

 

Hi @tomerfaith,

 

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

 

Thanks,
Xi Jin.

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.

 

77.PNG

 

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.

First thank for helping.
I have 2 problems:

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

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.