Filter in multiple columns

[omelei86]

Hi 

I am relatively new to PowerBI. Hope you can help. 

Table Opportunities

Opportunity ID	Sales Team (coming from Opp owner)	Sales Team (coming from Revenue responsible)
XjxjxjxhxA	Team A	Team A
XjxjxjxhxB	Team B	Team B
XjxjxjxhxC	Team A	Team B
XjxjxjxhxD	Team B	Team A

Now I'd like to apply a Filter visual on Sales team. However, it should filter on both columns. 

Let's say we filter on Sales Team = Team A, the expected result should be:

Opportunity ID	Sales Team (coming from Opp owner)	Sales Team (coming from Revenue responsible)
XjxjxjxhxA	Team A	Team A
XjxjxjxhxC	Team A	Team B
XjxjxjxhxD	Team B	Team A

Any idea? Thanks in advance..

[tamerj1 · Accepted solution]

Hi @omelei86 
Please refer to sample file with the solution https://www.dropbox.com/t/YXMM3TcOQdZ9yboj

First create the filter table 

Filter Table = 
SELECTCOLUMNS ( 
    DISTINCT ( 
        UNION ( 
            VALUES ( Opportunities[Sales Team (Opp owner)] ), 
            VALUES ( Opportunities[Sales Team (Revenue responsible)] ) 
        )
    ),
    "Sales Team", [Sales Team (Opp owner)]
)

Then Create new measure

Filter = 
COUNTROWS ( 
    FILTER ( 
        Opportunities, 
        Opportunities[Sales Team (Opp owner)] IN ALLSELECTED ( 'Filter Table'[Sales Team] ) 
            || Opportunities[Sales Team (Revenue responsible)] IN ALLSELECTED  ( 'Filter Table'[Sales Team] ) 
    ) 
)

Place it in the filter pane of the table visual then select "Is not blank" and apply.

[ALLUREAN]

Hi, @omelei86 

Please check attached file

[omelei86]

Thanks @ALLUREAN 

This is a step in the right direction. However, the values in my Opportunity table are not unique. I might have duplicate CombinedDescription values...

Is there any way to fix this?

[tamerj1]

Hi @omelei86 

you can create a separate filter table which you can use a a slicer.  Code for such table can be something like 

DISTINCT ( VALUES ( TableName[Column1] ), VALUES ( TableName[Column2] ) )

Then you may try creating a filter measure such as

COUNTROWS ( FILTER ( TableName, TableName[Column1] IN ALLSELECTED ( FilterTable[ColumnName] ) || TableName[Column2] IN ALLSELECTED  ( FilterTable[ColumnName] ) ) )

place it in the filter pane and select "Is not blank" and apply the filter.