“I am able to use each filter independently, but when I try to use multiple filters at once in the Filter Query it doesn’t work, what’s the right syntax in Power Automate?”
The Filter Query in ‘Get items’ action is one of the most powerful tools when working with SharePoint in Power Automate. Instead of processing all the items in your flow, you can take only the relevant ones. It saves API calls, flow run time, and makes maintenance of the flow easier. The filter works often fine when you filter using only a single column, but what if that’s not enough? What if you need to filter by multiple columns at once, e.g. by item status and date. Or by a date and status1 or status2?
Multiple filters with AND
When all of the filters must be true to return the item, you must connect the filters with AND operator. It doesn’t matter if it’s upper case or lower case, only the word and matters. For example, when building a reminder flow for opened tasks – due date in 2 days and status is ‘In progress’.
DueDate eq 'addDays(utcNow(),2,'yyyy-MM-dd')' and Status eq 'In progress'
If you’ve got more than 2 filters, all connected with and, you can add one after another.
filter1 and filter2 and filter3...Multiple filters with OR
The same logic applies also to filters where it’s enough if one of the filters is true – the OR operator. For example, to get only items where status is ‘In progress’ or ‘Waiting for something’.
Status eq 'In progress' or Status eq 'Waiting for something'
Again, to combine more than 2 filters, all connected with or, you can add one after another.
filter1 or filter2 or filter3...Combining AND and OR filters
The filters get more complicated once you start combining them together. If it’s a combination of AND and OR, you must get also their order right and define which filters belong together by adding brackets. For example, if you want a filter where due date is in 2 days and status is ‘In progress’ or ‘Waiting for something’, you’ll need brackets.
DueDate eq 'addDays(utcNow(),2,'yyyy-MM-dd')' and (Status eq 'In progress' or Status eq 'Waiting for something')Check the status first, if it’s ‘In progress’ or ‘Waiting for something’. Take the result of the filter inside brackets, and use it together with the and filter on due date.
If you don’t add any brackets, the evaluation will go from left to right. It’d process the first two filters on due date and status ‘In progress’, and then take the result and process it with or on the ‘Waiting for something’ status. That’s not what you want. If you’re not sure, don’t worry about using too many brackets, e.g.
(filter1 or filter2) and (filter3 or filter4)Just make sure to keep the filters that should be evaluated together in the same pair of brackets.
Summary
When using multiple filters in the Power Automate Filter Query, don’t overthink it. Just define the filters and connect them with and or or, depending on your needs. And if it’s a complex filter, add a few brackets to define which parts of the filter should be evaluated together.
And if the composed filter doesn’t work, try to check each filter separately. From my experience with complex filters it’s more often problem in one of the filters than in their combination.
Hi,
could you help to have a loot at the filter Query as below, seems it always ignore the part filter (Progress ne ‘Completed’ or Progress ne ‘Blocked’) automatic, what’s up, appreciate your reply, thanks!
DueDate eq ‘@{addDays(utcNow(),variables(‘varNumDays’),’MM-dd-yyyy’)}’ and (Progress ne ‘Completed’ or Progress ne ‘Blocked’)
Hello Jolin,
shouldn’t there be AND between the two Progress condition? As you have it now it’ll return ‘true’ because at least one of the conditions will be probably always true. For that part to be false the Progress would have to be Completed AND Blocked at the same time.
I have an error in the Get Items sections with my concat formula : concat(concat(concat(‘ExpirationDate’,’ ge ‘, ””,outputs(‘Today_+_x_days’)),””,’ and ‘, concat(‘ExpirationDate’,’ lt ‘,””, outputs(‘x_Days_+_30’),””, ‘ExpirationDate’,’ ge ‘, ””,outputs(‘Today_+_x_days’)),””,’ and ‘, concat(‘ExpirationDate’,’ lt ‘,””, outputs(‘x_Days_+_60’),””)))
its said : Bad Request
Hello Lina, value. You have to build simple filters (https://tomriha.com/what-to-enter-as-filter-query-in-the-power-automate-sp-actions/) and then connect them with the AND or OR.
the Filter Query has a strict limitation on the format – type in column name
Hi Tom
Great site for everything Power Automate. I am having an issue with this formula and not sure where I am going wrong. I have followed your guidance, but it keeps failing. Am I trying to put in too much into the formula?
field_6 le ‘@{addToTime(utcNow(), -360, ‘Day’, ‘yyyy-MM-dd’)}’ and (ResidualProbability ge ‘1’ or ResidualProbability le ‘4’) and (ResidualImpact ge ‘1’ or ResidualImpact le ‘3’) or (ResidualProbability ge ‘2’ or ResidualProbability le ‘4’) and (ResidualImpact ge ‘2’ or ResidualImpact le ‘4’ )
Basically, we use a 5 x 5 grid for a Risk Register Matrix, and I want to send an email reminder to Risk owners when the Residual Risk score is less than 8 and when the No of days since the Risk was reviewed is 360 days. I have it working when the Residual Risk Score is 16 or above and the No of days since the Risk was reviewed is 90 days. Not sure where I have gone wrong. Any guidance would be appreciated. KR Robin
Hello Robin,
my first thought would be to remove the ‘ ‘ around the numbers as that’s marking a string and you want to work with number. And if that won’t help, you could try to use the ‘Filter array’ action instead as that one seems more reliable to me lately, especially when comparing numbers: https://tomriha.com/how-to-apply-multiple-conditions-in-the-filter-array-power-automate-action/