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?
@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
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.
@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
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.
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")