How to filter on one of multiple values in one column?

[TheoM]

In my data model I have fact table, and in this table there is a column that can contain no, one or multiple values. If the column contains multiple values, the values are separated by ;

I want to filter the table on one or more single values.

Example: the column in my fact table looks like this:

A

null

A;B

B

A;B;C

C

A;C

When I filter on A, it should not only filter the rows that have A in the specified column, but also A;C, A;B and A;B;C

Best would be to create a relationship between the fact table and a table with all occuring single values, so in my report in can crossfilter, but I am not sure if that is possible.

I see similar problems on the forum, but none of them seems to fit perfectly. Can anyone help me with this?

[v-yulgu-msft · Accepted solution]

Hi @TheoM,

Here are two solutions for your reference. But the first one only works when you select a single value in slicer each time.

Solution1.

Suppose you have a fact table and an extra table which lists all available slier items.

Create measures:

selected item = SELECTEDVALUE(Test2[Column2])

check =
IF (
    NOT ( ISERROR ( FIND ( [selected item], SELECTEDVALUE ( Test1[Column1] ) ) ) ),
    1,
    0
)

Add measure [check] to "Visual Level filters" of table visual and set its value to 1.

Solution2

New a calculated table by crossjoin above two tables.

Test3 =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN ( Test1, Test2 ),
        "Isexist", IF (
            Test2[Column2] <> BLANK (),
            ISERROR ( FIND ( Test2[Column2], Test1[Column1] ) ),
            IF ( Test1[Column1] = BLANK (), FALSE (), TRUE () )
        )
    ),
    [Isexist] = FALSE ()
)

Add Test3[Column2] to slicer, add Test3[Column1] to table visual.

Best regards,

Yuliana Gu

[TheoM]

Hello Yuliana,

Solution 1 works. Solution 2 seems to work too, but I have to do a bit more work on my data model (define relationships and add a few more measures). I am sure I can make that work.

Thank you!

Best regards,

Theo

[Anonymous]

Hey @TheoM, 

What were the extra measures you needed to create to make this work?

I am having a hard time to do it. 

Thnaks, 

Sona

[Greg_Deckler]

Well, your best case scenario isn't going to work as far as I can tell. But, measures based around something like the IN operator would probably be the way to go.