Convert strings in table to distinct dropdown

[kriggo15]

I have a table which shows a user's relationship to a given item. I want to look at every item and get a distinct list of the user's roles with the intent of using those in a dropdown to filter a gallery.

I'm not really sure what the logic should be. I assume I'm going to have to filter the initial table on the current user and then maybe split the relationships up and do a distinct on that?

For example, if Jim was logged in, he would have a dropdown that only includes: Leader, Owner, User, Support.

Person	Item	Relationship
Jim	Table 1	Leader, Owner
Bob	Table 2	User, Support
Susan	Table 1	Owner
Jim	Table 3	User, Support
Jim	Table 4	Leader, Owner, Support

[rubin_boer · Accepted solution]

hi @kriggo15 

add this to your dropdown items 

Distinct(Split(Concat(Filter(YourDataSource, Person="Jim") ,Relationship,","),","),Result)

the example uses jim but you can replace that with a variable 

hope it helps

[RandyHayes]

@kriggo15 

Consider the following formula based on your data:

With({_table:
    Table(
        {Person:"Jim", Item:1, 	Relationship:"Leader, Owner"},
        {Person:"Bob", Item:2, 	Relationship:"User, Support"},
        {Person:"Susan", Item:1, 	Relationship:"Owner"},
        {Person:"Jim", Item:3, 	Relationship:"User, Support"},
        {Person:"Jim", Item:4, 	Relationship:"Leader, Owner, Support"}
    )},

    Distinct(
        Filter(
            Split(
                Concat(_table, Relationship & ", "), 
                ", "
            ),
            !IsBlank(Result)
        ),
        Result
    )
)

I hope this is helpful for you.