Power Apps: Filtering by Multi-select SharePoint Columns

No matter how long I work with the Power Platform, I’m always running into things which feel like they should be simple, but simply aren’t.

Recently, I needed to filter a SharePoint list as a data source which had a multi-select Person or Group column. It’s a simple idea, really. The list is one which I set up to contain a set of people within each department which play a particular role.

The list looks something like this:

You should get the idea. What I wanted to do was figure out if the current user shows up in any of the Business Segment Experts values for any department, and if so, give them specific rights to do certain things in the Power App. The hard part was the filtering.

If there are good articles out there about this, I couldn’t find them. (Videos just don’t work for me – maybe this is embedded in umpteen videos, but no Binglage found it if it is.) I finally figured it out, and so here’s yet another note to future me.

The solution is to use Ungroup either when first getting the information from the list or in subsequent steps.

I’ve put code similar to this in the onVisible event for the landing page for the Power App. This means it will run every time the user passes through the landing page. This should pick up any changes in the roles, which can be especially helpful in testing. I’ve numbered the sections in the code so I can explain it more easily below. (There’s no real formatter for PowerApps-ese, but surprisingly, JavaScript formatting works pretty well!)

// 1 - Department-based roles 
Refresh(Departments);
ClearCollect(DepartmentsCollection,Departments);
// 2 - Business Segment Expert
ClearCollect(BusinessSegmentExpertsCollection,Filter(Ungroup(ShowColumns(DepartmentsCollection, "ID", "Title", "BusinessSegmentExperts"),"BusinessSegmentExperts"), !IsBlank(Claims)));
// 3 - Find the user in the collection, if they are there
Set(thisBusinessSegmentExpert, First(Filter(BusinessSegmentExpertsCollection, Email = currentUser.Mail) ));
// 4 - Set a boolean indicating whether they are a BusinessSegmentExpert 
Set(isBusinessSegmentExpert, !IsBlank(thisBusinessSegmentExpert));
// 5 - Get info about the specific department for use in other filters, etc.
Set( 
     businessSegmentExpertDepartment,
     {
         Title: If(
             !IsBlank(thisBusinessSegmentExpert),
             thisBusinessSegmentExpert.Title,
             Blank()
         ),
         Id: If(
             !IsBlank(thisBusinessSegmentExpert),
             thisBusinessSegmentExpert.ID,
             Blank()
         )
     }
 ); 

Section 1

There are other columns in the Departments list I need to work with, so I first pull all the items into a collection with ClearCollect. The list isn’t ever going to have more than a few dozen items, but it’s more efficient to work with a collection than to continually go back to the list if you don’t need to.

Section 2

In this section, I’m pulling only the columns I need for this specific task into a collection, and I’m using the Ungroup function to transform the data to make it useful.

Let’s step through the piece parts.

First, I pull out only the columns I need:

ShowColumns(DepartmentsCollection, "ID", "Title", "BusinessSegmentExperts")

This gives me a table with just the columns ID, Title, and BusinessSegmentExperts. It makes the table I’m working with much less cluttered, thus easier to look at to understand it.

Next, I take the results of that and Ungroup the BusinessSegmentExperts.

Ungroup(...result from above...),"BusinessSegmentExperts")

That’s the magic bit. That transforms the info from a table of people to a set of rows of people. If I’m the only person in the multi-select column, it goes from this:

to this:

The ID and Title columns are off to the right – right in line with the people, and repeating for each person!

In essence, we go from this:

to this:

Section 3

The order of the columns above doesn’t really matter, and I can now filter by the user’s Email. Here I’m setting a variable to the current user’s item in the list, if there is one. By using First, I’m “flattening” the item from a table into a record. I know there should only be one entry per person (they can only play this role in one department); you obviously need to understand your data.

Set(thisBusinessSegmentExpert,First(Filter(BusinessSegmentExpertsCollection, Email = User().Email) ));

Section 4

In this line, I’m setting a boolean to indicate if the current user is a Business Segment Expert.

Set(isBusinessSegmentExpert, !IsBlank(thisBusinessSegmentExpert));

Once the isBusinessSegmentExpert boolean is set, I can use it to determine visibility of buttons, let it determine filters for other collections, etc.

Section 5

Finally, in Section 5, I’m grabbing the department for which the current user plays this role, if any. For sake of debugging, I’ve grabbed both the Title and the ID separately and set a variable with those values. It’s useful to be able to display the Title for readability, of course, but since I use the use the department as a lookup column, I use the `ID` for filtering.

Set( 
     businessSegmentExpertDepartment,
     {
         Title: If(
             !IsBlank(thisBusinessSegmentExpert),
             thisBusinessSegmentExpert.Title,
             Blank()
         ),
         Id: If(
             !IsBlank(thisBusinessSegmentExpert),
             thisBusinessSegmentExpert.ID,
             Blank()
         )
     }
 );

Elsewhere in the app, I can now use businessSegmentExpertDepartment.Title or businessSegmentExpertDepartment.Id anytime I need them.

---

There are a lot of pieces to all this, but the key learning from this for me was the power of Ungroup.

An unfortunate thing is the way you go about doing this in Power Apps is fundamentally different than how you go about doing it in Power Automate (flow). This is one of the reasons it’s tough for me to think about these different apps as part of a power “platform”. They are just so different.