distinct filtering with split

[BenSt]

I have a dropdown I would like to pull from a sharepoint (automate roster test). The column (tsd engagement teams) has cells that have multiple items in them (ex. all hands; women in tech; skills & training). As it stands now I can get a distinct list with this code 

ForAll(Distinct(Automate_Roster_Test,'TSD BCM Engagement Teams'), {result:ThisRecord.Value})

But the issue is that the dropdown options are the entire cell content as exampled above. I tried to collect with a split, the semicolon as the delimiter using this formula 

Distinct(Automate_Roster_Test,Split('TSD BCM Engagement Teams', ";"))

But that didn't work, the error seems to be with the semicolon. Are there any suggestions to help me achieve this goal? 

[LaurensM]

Hi @BenSt,

Could you try the following code below:

Distinct(
    //Only show the unique split values
    Ungroup(
        //Ungroup the table of split values
        ForAll(
            //Loop through list & return a table of split values
            Automate_Roster_Test,
            Split(
                'TSD BCM Engagement Teams',
                "; "
            )
        ),
        "Value"
    ),
    Value
)

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[BenSt]

This does not split up the results by the semicolon. I still see multiple values in the dropdown 

[LaurensM]

Hi @BenSt,

Your text example had a semicolon followed by a space - so I assumed that this would be the separator. If your items only have a semicolon as a delimiter, please remove the space from my separator within the split function:

//Code....
            Split(
                'TSD BCM Engagement Teams',
                ";"
            )
//Code...

I hope this helps!

[BenSt]

That works thank you! this creates another issue though. That specific dropdown 'engagement drop' ties into another dropdown with this formula: 

Distinct(
Filter(Automate_Roster_Test,
(
Len('Loc Dropdown_5'.Selected.Result)=0||
Location='Loc Dropdown_5'.Selected.Result
)&&
(
Len('Rank Dropdown_7'.Selected.Result)=0||
Rank='Rank Dropdown_7'.Selected.Result
)&&
(
Len('Domain Drop'.Selected.Result)=0||
'Domain Level'='Domain Drop'.Selected.Result
)&&
(
Len('Engagement Drop'.Selected.result)=0||
'TSD BCM Engagement Teams'='Engagement Drop'.Selected.result
)
),
ColleagueName_Hide
)

When I put your formula into the engagement drop it does not populate the dropdown with the names i need associated with the engagements 

[LaurensM]

@BenSt that might be because Split & Distinct no longer return .Result as an output but rather .Value. Could you try replacing the .Result for that dropdown:

Len('Engagement Drop'.Selected.Value) = 0 ||
'TSD BCM Engagement Teams'='Engagement Drop'.Selected.Value

Could you give that a try? 😊

[BenSt]

Thank you for your help but it still has the same result. 

[LaurensM · Accepted solution]

@BenSt my apologies, we simply have to change the logic for that particular condition.

We now no longer want to check whether the text equals the text in that column - since it never will (we've split the concatenated text field into separate values)

We want to check whether the split value appears in that column:

Len('Engagement Drop'.Selected.Value) = 0 || 'Engagement Drop'.Selected.Value in 'TSD BCM Engagement Teams'

Unfortunately this will make your Filter non-delegable. Should this be an issue (list > Data Row Limit in your app settings), we can avoid this by prefiltering the data with delegable conditions. The first Filter would include all conditions except the one above, the outer Filter would only have the condition above:

Distinct(
    Filter(
        Filter(
            Automate_Roster_Test,
            IsBlank('Loc Dropdown_5'.Selected.Result) || Location = 'Loc Dropdown_5'.Selected.Result,
            IsBlank('Rank Dropdown_7'.Selected.Result) || Rank = 'Rank Dropdown_7'.Selected.Result,
            IsBlank('Domain Drop'.Selected.Result) || 'Domain Level' = 'Domain Drop'.Selected.Result
        ),
        IsBlank('Engagement Drop'.Selected.Value) || 'Engagement Drop'.Selected.Value in 'TSD BCM Engagement Teams'
    ),
    ColleagueName_Hide
)

I also made some smaller readability improvements to your code.

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!

[BenSt]

That works! thank you very much! 

Sorry 1 last question if I can 

Do you know why I'm getting 2 blank options?

[BenjaminJarry72]

I use the same solution on my need, but get a list populated with an empty value...any chance to help me on that ?