Using Linktofilteredview() by checking value/values in list based on user input

[Diego_Rodriguez]

Hi there!

I´m using Linktofilteredview().

I have a table (Table A) I want to filter based on a specific column [Meso] through user input (Form A).
Column [Meso] is an EnumList with the following possible values (M1,M2,M3,M4), with “comma” as the separator.
User can select one or all those values using Form A.

The idea is to filter all rows in Table A where the value or values inputted by the user are present. I have tried to use an expression with “IN”, but Linktofilteredview() only gives back the exact pattern (For example: If the user select “A”, we doesn´t obtain any result.This is wrong because we have rows in Table A with [meso] = “A,C”. The desired output for that input would be all rows where “A” is present.

What can I do? Thank you very much in advance,
Diego

[Aleksi]

What is the formula you have tried to use?

[Diego_Rodriguez]

Hi Aleksi:

This is the formula:

LINKTOFILTEREDVIEW(“Table A”,IN([meso],[_THISROW].[meso]))

[Aleksi]

What is your “meso” column type in table A?

[Diego_Rodriguez]

[meso] in Table A is EnumList and [meso] in Form A is also an EnumList

Initially, I tried [meso] in Table A to be Text…buy I had some problems with LINKTOFILTEREDVIEW() comparing with the input from the form (List). So I assigned both columns to be EnumList. Now LINKTOFILTEREDVIEW() works…but not the way I want.

Is there any other way to filter Table A based on this column? I have seen something about Slices…but I started with this option.

[Aleksi]

The problem is your filter. You are trying to compare list against list and IN expression is not meant for that. Let’s say you have options 1,2,3 selected in both columns. IN formula will make three different comparings… 1,2,3=1, 1,2,3=2 or 1,2,3=3. As you can see the answer is always false.

[Diego_Rodriguez]

Thank you Aleksi.

So, what are the available alternatives for this case?

[Steve · Accepted solution]

Try this:

LINKTOFILTEREDVIEW(
  "Table A",
  (
    COUNT([meso] - LIST())
    <> COUNT([meso] - [_THISROW].[meso])
  )
)

1. COUNT([meso] - LIST()) counts the distinct items of [meso]. Subtracting one list from another has the side-effect of removing duplicate list items.

2. COUNT([meso] - [_THISROW].[meso]) counts the distinct items of [meso] that are not also present in [_THISROW].[meso].

3. (COUNT(...) <> COUNT(...)) asks whether the count of (distinct) items in [meso] changes if items also in [_THISROW].[meso] are removed. If [_THISROW].[meso] contains items also in [meso], the counts will be different.

[Diego_Rodriguez]

Fantastic Steve, it works! Thank you very much and have a nice day