Answered

Filter with multiple conditions using Excel as data source

(0) Share

Report

Posted on by David_Morris

I have a simple Excel Spreadsheet as my source data, and a DataTable control on a Canvas app. I am having trouble filtering with two conditions. I see many posts on this and they look quite easy to follow, but I am not seeing what I am doing wrong here, because when I apply two conditions, the table shows "we didn't find any data to show at this time".

My excel spreadsheet is as below (with just two rows in the sheet):

Column1	Column2
a	a
a	b

When I filter with one condition I get two rows returned which is correct.

Filter(Table1, Column1 = "a")

If I change this (still with one condition) to filter on Column 2 I get one row which is also correct.

Filter(Table1, Column2 = "a")

However if I combine these two conditions together on the filter it does not return any rows at all.

Filter(Table1, Column1 = "a", Column2 = "a")

Can you please help me understand what I am doing wrong here?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

BlessedCobba 459 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @David_Morris

you will want to use an & symbol instead of the , if you want both conditions to be true
if you want it for either conditions then you will use OR

Filter(Table1, Column1 = "a" & Column2 = "a") Filter(Table1, Column1 = "a" OR Column2 = "a")

Was this reply helpful? Yes No
David_Morris 14 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the response @BlessedCobba , but I don't believe that is the right solution unfortunately.

The help docs on the Filter command suggests the below, which shows a comma separated list of all logical tests like what I had in the original post.
Filter(Source, logical_test, logical_test, …)

However, I tried your suggestion anyway, which shows a delegation warning on the "=" and "&" portions of the condition, and also does not return any records.

Was this reply helpful? Yes No
BlessedCobba 459 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@David_Morris
My Apologies, when doing a filter it uses a &&

You are correct that a comma can be used (i was not aware), I have just always used && instead

Not sure why yours is returning no results, I have tried it both ways and it seems to work, my only guess is something to do with the data source, i just used a test collection for mine

Was this reply helpful? Yes No
Verified answer

David_Morris 14 on at

Like (1)

Report
Copy link

Link copied!

Thanks @BlessedCobba , using "&& has worked, however that presents a delegation warning which is not optimal 😞

I tried myself with a collection and can confirm it works.
Collect(Table2,{Column1:"a",Column2:"a"},{Column1:"a",Column2:"b"});

And the original filter command I was using works and returns one row correctly.
Filter(Table2,Column1="a", Column2="a")

When swapping back to the excel data source as per the original post, I cannot filter on both Column1 and Column2 at the same time using the above filter statement (which is delegable). While using the "&&" has worked, it is not delegable and therefore not optimal.

I will mark your response as an accepted solution given it has in fact worked per the original post, but given the solution throws a delegation warning, I would love to hear if there are any other answers worth trying.

Was this reply helpful? Yes No
BlessedCobba 459 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@David_Morris

A general rule is excel should only be used as a data source for learning how to PowerApps. if you're going to be making an app for use then at a minimum you should be using SharePoint as a data source

you can export / connect to a SharePoint list from excel if people need to use the data in that way but should never really be the basis of a power app data source

Was this reply helpful? Yes No