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)

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..

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.

     

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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. 

    • omelei86's avatar
      omelei86
      Frequent Visitor
      4 years ago

      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?