cancel
Showing results for
Did you mean:
Regular Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super Contributor

## Re: How to filter on one of multiple values in one column?

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 (
CROSSJOIN ( Test1, Test2 ),
"Isexist", IF (
Test2[Column2] <> BLANK (),
ISERROR ( FIND ( Test2[Column2], Test1[Column1] ) ),
IF ( Test1[Column1] = BLANK (), FALSE (), TRUE () )
)
),
[Isexist] = FALSE ()
)```

Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User

## Re: How to filter on one of multiple values in one column?

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.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Highlighted
Super Contributor

## Re: How to filter on one of multiple values in one column?

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 (
CROSSJOIN ( Test1, Test2 ),
"Isexist", IF (
Test2[Column2] <> BLANK (),
ISERROR ( FIND ( Test2[Column2], Test1[Column1] ) ),
IF ( Test1[Column1] = BLANK (), FALSE (), TRUE () )
)
),
[Isexist] = FALSE ()
)```

Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: How to filter on one of multiple values in one column?

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

Frequent Visitor

## Re: How to filter on one of multiple values in one column?

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

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 45 members 1,215 guests
Recent signins: