Filter Gallery Results (from Sharepoint list) based off of collection value

[p0werSn3k]

I'm trying to filter the gallery results based off a collection value. The below example works - 

Filter([@'SharePointList'], 'Value1orValue2'.Value = "Value1")

Instead of the string "Value1" I want to filter from the value of a collection item - FilterList.Value1orValue2 but I keep getting errors.

I've tried -

Filter([@'SharePointList'], 'Value1orValue2'.Value = FilterList.Value1orValue2)

and

Filter([@'SharePointList'], 'Value1orValue2'.Value = First(FilterList.Value1orValue2)

and some variations of those but no luck. Any advice?

FYI the sharepoint list has a column title Value1orValue2 with two choices. The collection - FilterList has the same column title & possible values.

[KrishnaV]

Hi @p0werSn3k ,

try this:

Filter([@'SharePointList'], 'Value1orValue2'.Value = First(FilterList.Value1) Or 'Value1orValue2'.Value = First(FilterList.Value2))

 
I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[p0werSn3k]

That doesn't work. It shows the '=' as an invalid operator type. If I use 'in' for the operator I get a 'Cannot use Record values in this context' error

[KrishnaV]

Hi @p0werSn3k ,

sorry that is my bad, try this:

Filter([@'SharePointList'], 'Value1orValue2'.Value = First(FilterList).Value1 Or 'Value1orValue2'.Value = First(FilterList).Value2)

 
I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

[v-xida-msft]

Hi @p0werSn3k ,

Could you please share a bit more about the "Value1orValue2" column? Is it a Choice type column?

Do you want to filter your Gallery Items based on the collection value (FilterList)?

According to the needs that you mentioned, I think there is something wrong with your Filter formula. Within your Filter formula, the 'Value1orValue2'.Value formula returns a Text value, but the FilterList.Value1orValue2 formula returns a Table value. You could not compare a Text value with a Table value using '=' operator directly.

I have made a test on my side, please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       [@'SharePointList'],
       Value1orValue2.Value in Concat(FilterList, Value1orValue2.Value & ";")
)

or

Filter(
       [@'SharePointList'],
       Value1orValue2.Value in ForAll(
                                      FilterList,
                                      Value1orValue2.Value
                               )
)

Please consider take a try with above solution, hopes it could solve your problem.

Best regards,

[p0werSn3k]

@v-xida-msft Yes, Value1orValue2 is a choice type column & I want to filter the gallery items based on the corresponding collection value. I'll try your solution and report back. 

[v-xida-msft]

Hi @p0werSn3k ,

Have you taken a try with the solution I provided above? Is it helpful?

Please take a try with the solution I provided above, then check if the issue is solved. If you have solved your problem, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

[p0werSn3k · Accepted solution]

Filter([@'SharePointList'}, Value1orValue2.Value in Concat(FilterList, Value1orValue2) 

 Ended up working. Thanks for the advice!

[v-xida-msft]

Hi @p0werSn3k ,

Have you solved your problem? Is the solution I provided above helpful in your scenario?

If the solution I provided above is helpful in your scenario, please also consider go ahead to click "Accept as Solution" to identify my reply as Solution.

Regards,