Multi-column filtering : OR not AND

[djohanlon]

Hello

I have a sales commission table in which people can have comission from manufacturers, suppliers and/or distributors, that has the basic following structure:

Manufacturer	Sales Rep	Percentage	Supplier	Sales Rep	Percentage	Distributors	Sales Rep	Percentage
Big toys	John	5%	Allanong	John	5%	Toy Joy	Peter	5%
Small toys	Peter	10%	Suppy	John	5%	Toys4U	Giles	5%
Medium Toys	John	5%	Suppllyer	Giles	5%	Toy Joy	Peter	5%

How do I set up filtering so that I can select to list me all rows that contain one of the Sales Reps, not matter which column they are in, irrespective of who is in the other columns?

Is there an easier/more logical way than creating a column that would concatenate the names (ie John John Peter, John Giles Peter, etc) and then selecting the sort based on a filter that would says "if contains" and the name?

Thanks

Daniel

[Anonymous · Accepted solution]

Hi, @djohanlon 

Thanks for lbendlin and Ashish_Mathur reply. You can write the following measure, and put it into filter pane to filter.

Measure

Flag_SlaesRep = 
VAR _name =
    SELECTEDVALUE ( 'Name'[Sales Rep] )
VAR _salesRep =
    SELECTEDVALUE ( 'Table'[Sales Rep] )
VAR _salesRep_1 =
    SELECTEDVALUE ( 'Table'[Sales Rep_1] )
VAR _slaesRep_3 =
    SELECTEDVALUE ( 'Table'[Sales Rep_3] )
VAR _flag =
    IF (
        _name = SELECTEDVALUE ( 'Table'[Sales Rep] )
            || _name = SELECTEDVALUE ( 'Table'[Sales Rep_1] )
            || _name = SELECTEDVALUE ( 'Table'[Sales Rep_3] ),
        1
    )
RETURN
    _flag

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Ashish_Mathur]

Hi,

If it isn't too much work for now, then try this aproach

1. Create 3 seperate tables (Manufacturer, Supplier and Distributor) of 3 columns each
2. Rename the first column to Name
3. Insert a column in each table with the heading of Partner and ensure that each entry there has Manufacturer, Supplier and Distributor
4. Append the three tables

If this is too much work for you, then this can be automated in Power Query as well.  Let me know.

[lbendlin]

Unpivot your source data to bring it into a usable format.

Type	Name	Sales Rep	Percentage
Manufacturer	Big toys	John	5%
Manufacturer	Small toys	Peter	10%
Manufacturer	Medium Toys	John	5%
Supplier	Allanong	John	5%
Supplier	Suppy	John	5%
Supplier	Suppllyer	Giles	5%
Distributors	Toy Joy	Peter	5%
Distributors	Toys4U	Giles	5%
Distributors	Toy Joy	Peter	5%

Then you will need to use measures to implement the OR logic (default is AND)