cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TheoM Regular Visitor
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
v-yulgu-msft Super Contributor
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.

1.PNG2.PNG

 

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.
3.PNG
 
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 ()
)
4.PNG
 
Add Test3[Column2] to slicer, add Test3[Column1] to table visual.
5.PNG
 
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
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
v-yulgu-msft Super Contributor
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.

1.PNG2.PNG

 

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.
3.PNG
 
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 ()
)
4.PNG
 
Add Test3[Column2] to slicer, add Test3[Column1] to table visual.
5.PNG
 
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.
TheoM Regular Visitor
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

sonamyan Frequent Visitor
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

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 45 members 1,215 guests
Please welcome our newest community members: