Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Filter a filter

Hi everyone,


i am new to powerapps and maybe the solution is easy.


I want to create a prefiltered view on Projects.




If a users opens the app a userId will be looked up( ---> this works)



I store this value in a textbox named varUserId



with this userId Filter a m:n table where are useres assigned to projects so the result will

a set of project ids the user is assigned to

Filter('[dbo].[tblProjectUser]';fk_user_id = Value(VarUserID.Text))

When i display this in a DataTable it works.


How can i use this to prefilter the gally datasource tblProjects to see only the  Projects assigned to the user?


Thank you!




Hi nickduxfield,


thank you for this great idea. you even helped my by discovering the onStart method.

I tried to use you explanation and it worked fine until point 2. there i have the problem, when i try to filter the projects by the user project i always get an error.

I think the reason is, that the ProjectUserCollection returns more than one value.

Any further ideas?


Thank youn and kind regards

Wow thats great,

Lets try and solve this, it is a data model I know people will have difficulty with because we are aways looking for everything on one table.


You need to investigate, what exactly the error says. Hover your cursor around the code or and warnings and see what its saying.

I dont know your data source (SharePoint List, Excel).


  • If you dont have a 'Modified' (date) column you'll have to remove the Sort by Columns until you have a column you can use.
  • Maybe you can tell me the column types, text, number etc.
  • Open up the file menu, and look at the collections you created. See if they actually exist and i the way you anticipated.


@grunwald Is your data model fixed? Do you have exisiting data or is this from scratch?

From what I can see without putting some limits on your method, you may have to rethink your data model.


You can start with a user, thats solved, you dont need a list.

You need only one or two lists as far as I can see.

Projects and ProjectRegistry


Projects list:

A place to register all projects and associated project info.


Project Registry:

A place to bring together User info and the Project from the Projects List.

  • You can use an additional textinput with a dropdown on a card to lookup project titles. Other associated fileds like 'Budget' can be autofilled using 'Lookup()'.

Then you will be able to pre-filter.


From what I can see, however hopeful we are about collections filtering collections, I'm not certain I can work yet.

Happy to talk more about it.



Hi nickduxfield,


we solved it!!!


i tried it like you described

it has german writing, but i think everybody gets it.


  1. OnStart ClearCollect(varSet;Set(curUserID; LookUp('[dbo].[tblBenutzer]';User().Email = email;ID));Collect(projectCol;'[dbo].     [tblProjekt]');Collect(userProjectCol;Filter('[dbo].[tblProjektBenutzer]';fk_benutzer_id=curUserID)))
  2. Gallery Items Filter(projectCol;ID in userProjectCol.fk_projekt_id)


You can compare type Collection with the in operator.


Thank you so much for your help




Btw. my datasource is an azure sql server

Thats amazing, I setup something similar in SharePoint lists to get to the bottom of it but mine failed.

Perhaps delegation for type collection only works in Azure databases. Wow, that really opens doors! Fantiastic!

Il try the IN operator in my setup.

Tried the In Operator, Works!

Can you accept as a solution to help by brownie points.

Advocate I
Advocate I


Hi I am trying to filter a filter. and this is how it looks like. if someone could help on thing issue it would be a great help. thanks in advance. You can also Email me on my personal Email. Thanksexport.png

Not applicable

I'm just having fun with the title. Filter a filter. lol. There's probably a better way of saying that.


In any case, @DRCICI_Dev -- 

As suggested in the previous posts, use the in operator to simulate joins in PA. Or AddColumns(). There's a bunch of posts about it in this community.


On second thought, I absolutely have no idea what you're trying to do with this bit of code:

Filter(Participant, ID <> 'Attended Event'.ParticipantID && 'Attended Event'.PrimaryId = eventGallery.selected.PrimaryID)


  • Do you want to get the list of events that a particular participant hasn't attended?
  • Or do you want to get the list of participants that have NOT attended a particular event?


 Edit. Minor typos.

Hi @Anonymous. Thanks for the reply. Sorry for the very funny title.

I am trying to get the the participant who have not yet attended the event.


I Just read the documentation of columns on powerapps. I see the best way was ShowColumns.

Filter(Participant, ID <>  ShowColumns('Attended Event', "ParticipantID"))


I triend even a simple way to get the Participant. But it did not filter it properly.


I also did not include the filter on the event cause it would be better if we have a condition on the ShowColumns.


Not applicable

@DRCICI_Dev --

First of all, the title's nobody fault. No need to apologize over it. I'm actually glad it got me to laugh. So, on to the code!


Okay. I admit. I haven't even known there was a function such as ShowColumns(), although I see how it can be useful in certain situations. However, to do the thing you want to do, it's not required.


So, given an event -- find out the participants who haven't attended that event. Try this.

Filter( Participant,
    !( ID in Filter( 'Attended Event', 
        PrimaryID = eventGallery.Selected.PrimaryID
    ).ParticipantID )

I nested the Filters together just so you could use it directly in a property like Items. This is certainly not going to be delegated (because of the nesting) but then again, I can't think of a way here to ensure delegation and still get what you want (because you definitely need the in operator or some variation of it). 


Take the code with a grain of salt, by the way. I don't exactly know what the names are for your objects, columns, and such so I extrapolated. Revise it as needed.


Lemme know how it goes.


P.S. There's a lot of similar questions posted in this community about this topic. I'm not going to link those posts (frankly because I'm too sleepy right now, lol) but look for the following keywords -- Relational Data, Joins, Parent-Child Relationships, LookUp Fields, etc.


P.P.S. Just a suggestion -- Next time you have a problem with PowerApps dev't, create another thread/post. It just seems like your problem isn't getting that much attention and that may be caused by the fact that the problem in the first post is indicated to be solved. @mackenzie_lyng -- Is it possible to migrate a part of this thread onto a new one at the behest of the posters?


Edit: Forgot a minor point. Don't snooze and type, guys!

Thanks @Anonymous it's working but powerapp found some error. I will go check the title you are telling me. so i could see the best or proper way to filter it.  just so everybody will be on the loop.

the warning message


" Part of this Filter formula cannot be evaluated remotely due to service limitations. The local evaluation may produce suboptional or partial results. If possible, please simplify the formula. For more information, please see the filter function documentation.

by the way @Anonymous what document are you look at. cause I have never seen the impression "!" or in coding terms "not empty" or "not in" on the documentation of powerapps. I am new at this. I have been working on powerapps for 2-3weeks now. and I have already create a working system but I want to improve it more. 1 example is this question I cannot find a solution on documentation and also I think i had a hard time looking for the proper keywords. So I was not looking hard enough. 

Helpful resources

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.


Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (5,729)