Forum Discussion

omelei86's avatar
omelei86
Frequent Visitor
4 years ago
Solved

Filter in multiple columns

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) X...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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.

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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.