I'm working on a survey PowerApp that's sourced from a Sharepoint list of questions. There will be multiple respondents during the survey, and depending on their role, they will or will not get asked each question.
The problem I'm struggling with is, I have a "filter answered questions" checkbox that I'm struggling to filter out ONLY after two or more people answer a question targeted to two or more people. I'm creating a collection of answeredquestions, and if I use the following filter, it will filter the question as soon as one of the two respondents answers the question (because of the Not(ID in answeredquestions.QuestionID). I want it to filter the question out only if there's a question from ALL respondents.
Filter(colSurveyQuestions, Not(ID in answeredquestions.QuestionID) &&
Role in PersonaTargets.Value && ID in interviewees.ID
Without knowing a lot about your data structure, I'll suggest this: Can you add a column in one of your SP lists that tracks the number of times a question has been answered? Then, you could filter out questions using that column as one of your criteria. It would save building extra internal collections.
Would that fit with your app?
@BCLS776 Unfortunately not because these questions are going to be asked over and over to many different people, but only 1-5 people will be in the room at a time, so I have a listbox that shows the names of the people who have a persona that applies to each question and then each question is saved to a single datasource. I basically want a way to check to see if 100% of the 1-5 people in the room have completed each question or not. I'm doing a countrows comparison to make the Fill color of each gallery item change which is working, but I'm struggling with how to filter the completed ones out of the gallery completely. If I could somehow use the same CountRows check to cause them to filter, that would probalby be easiest, but not sure if that's possible.
OK, I understand better the need for working with internal collections in your case.
So, let's work on the logic of your filters. Here's your code again:
Filter(colSurveyQuestions, Not(ID in answeredquestions.QuestionID) && !IsEmpty( Filter(interviewees, Role in PersonaTargets.Value && ID in interviewees.ID ) ) )
In words, I'll describe how I read this:
The stuff in the Lookup() described in 2 above has some issues. I believe you are trying to check if colSurveyQuestions has records applicable to the Role from interviewees, but that isn't what is happening.
Assuming PersonaTargets originates from a choice column with multiple selections, this code should work better:
Filter(colSurveyQuestions, Not(ID in answeredquestions.QuestionID) && !IsEmpty( Filter(interviewees, Role in Concat(colSurveyQuestions.PersonaTargets,Value,", ") ) ) )
I removed the part about checking interviewees.ID - I didn't understand the purpose of that. You may have a good reason to filter based on ID, but it should not be circular as before.
Try it out and let us know how it goes,
Join us for the next call on August 17, 2022 at 8am PDT.
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.