Distinct values of a SharePoint list column that allows multiple selections

[SachS]

I have a SharePoint list with two fields (among others), both of which are of type "Person or Group", with selection set to "People Only". Columns are, [PrimaryUser] and [SecondaryUsers]. The difference is:

• PrimaryUser - Allow multiple selections = false
• SecondaryUsers = Allow multiple selections = true

Essentially, I can select multiple users within the organization for the SecondaryUsers column.

In my app, I want to have two ComboBox controls, bound to each of the two columns above, and display distinct values from each. Suppose my list data looks like this:

PrimaryUser	SecondaryUsers
A	B,X
B	Y,X
B	Y
B	Y

So I want the two combo boxes to have following data:

ComboBoxPrimaryUser = A, B

ComboBoxSecondaryUsers = B,X,Y

I can achieve the desired result in the first combo box easily:

Distinct(
    MyList,
    PrimaryUser.DisplayName
)

However, if I did the same in the SecondaryUsers combo as shown below, I get a list of 3 items (as expected) but text is blank. I made sure that the ComboBox Data card Layout = Single. 

Distinct(
    MyList,
    SecondaryUsers.DisplayName
)

I assume this is because SharePoint turns the SecondaryUsers column into something like a list of lists when the 'Allow Multiple Selection' property is turned on. This is probably confirmed by the fact that I get only 3 items, not 4, since last two items in my list are identical.

Is there a way to get distinct items from the SecondaryUsers column? I tried the following but it doesn't fix the problem either, and this time I get a list of 2 empty items, not 3.

Distinct(
    MyList,
    Distinct(
        SecondaryUsers,
        DisplayName
    )
)

[RandyHayes · Accepted solution]

@SachS 

Your formula would be:

Distinct(
    Ungroup(
        ForAll(MyList, {Item: SecondaryUsers}),
        "Item"
    ),
    DisplayName
)

This will give you a distinct list of all of the multiple person choices.

I am not clear on your example where you state that PrimaryUser is not multiple choice, but then you state users A and B selected.  That would not be possible if not allowing multiple choices.

I hope this is helpful for you.

[SachS]

@RandyHayes got it, perfect.

The table I showed is basically the contents of the list - in this case there are 4 records. PrimaryUser doesn't required data to be unique, just that you can only select one 'Person' per record. On the other hand SecondaryUsers can select multiple 'Person' per record.

[SachS]

Thought I'd add a little bit more useful information for someone else who might look for the same answer later.

My goal was to filter a gallery using the SecondayUsers selected on a combo box, so here's how to do that.

SecondaryUsersComboBox.Items

Distinct(
    Ungroup(
        ForAll(
            MyList,
            {Item: SecondaryUsers}
        ),
        "Item"
    ),
    DisplayName
)

Gallery.Items

Filter(
    MyList,
    cmbSecondary.Selected.Result in SecondaryUsers.DisplayName || IsBlank(cmbSecondary.Selected.Result)
)

cmbSecondary.Selected.Result in SecondaryUsers.DisplayName will filter items based on combo box selection. The IsBlank(cmbSecondary.Selected.Result part is to ensure the gallery displays all items when nothing is selected in the combo box.

[hsfournier]

 This is great, but I would like to Sort by name, and I can't seem to make that work. I think it's because Ungroup turned everything into plain text. Any ideas?

[Sbassmed]

Hey this Help me  a lot, I´ve a column type "User" and I want to check all the user´s without duplicate values to show them into a ComboBox ... my Question here is if I´d like to see the email from each user   that´s not duplicate it´s mandatory to create a collection or not?  (so when someone select the name of the user on the ComboBox the value will be their email)

[hsfournier]

Hi Sbassmed, I think I understand what you are asking. My first question is how is the User column set up on SharePoint, do you have it set-up as a People column? If so, you have access the User's email. I just did a test on my side and you can create the Distinct list right from the data source in the combo box without having to create a collection first.

For the combo box

Sort(Distinct(SharepointListName,User.Email),Value)

For the gallery to filter

Filter(SharepointListName,ComboBox1.Selected.Value =User.Email)

Hopefully, I understood you question correctly, and this helped.

[Sbassmed]

Hello thanks for your reply , 

The column is  a "People/User column", so for instance on the combobox when I used the Choices([@SP_list].Columm) I´ve access to the user name , email , department etc.. 

With that in mind the thing that I´d like to achieve is that  my combobox shows me the  values without duplicates in this case displaying the user name , but once I selected the user I also could have the email .. I´m having troubles trying to achieve this .. currently this is the syntax that i´m using in the Items property 

Distinct(
Ungroup(
ForAll(
SP_List;
{UserUnique: SP_Column}
);
"UserUnique"
);
Email
) 

---> This returns me a table <-----

With this Syntax I´m able to get the user´s without duplicates but the value will be the email or the name ,(if I change it for DisplayName)  so when I Select and option from the Combobox  by the name I hope to get the email value as well ... 

[Larsen]

I stumbled my way through this problem with a similar formula, but same outcome. Cool how you can do that in PowerApps. Anyone know of a good way to get this group of individuals into a gallery? Trying to show who all has volunteered for various events, but coming up short. Currently trying to use Office365Users.SearchUserv2 to create a gallery of these individuals.

Sort(
    FirstN(
        Distinct(
            Ungroup(
                colVolunteers,
                "Volunteers"
            ),
            Email
        ),
        1000
    ),
    ThisRecord.Value
)