Multi-selection filter applied on multiple fields

[attilajozsef712]

Hi!

I have a datatable where I have transaction information. I want to set filters on club names (so selecting club A and club B) and show all related rows in the table whether the clubs are seller or buyer in the transaction (so if a club sold a player that is a distinct line in the table and if the same club bought a player that is an other distinct line). I also want to have a possibility to select multiple regions or countries and using them as a filter on my table.

The dataset is not so complex, I have a seller company and a buyer company, both have region and country information and the fe of the transaction and the target of the transaction. 

[attilajozsef712]

Country A	Region A	Club Seller	Country B	Region B	Club Buyer	Target	Transfer Price
China	Asia	Club A	England	Europe	Club C	Player A	10000000
Brasil	South America	Club B	England	Europe	Club C	Player B	2000000
England	Europe	Club C	France	Europe	Club D	Player C	15000000
England	Europe	Club C	Spain	Europe	Club E	Player D	12000000
France	Europe	Club D	Italy	Europe	Club F	Player E	6000000
France	Europe	Club D	Germany	Europe	Club I	Player F	75800000
France	Europe	Club D	Belgium	Europe	Club J	Player G	13000000
USA	North America	Club G	Brasil	South America	Club B	Player H	2650000
Egypt	Africa	Club L	Australia	Australia	Club Z	Player I	120000

[v-kelly-msft · Accepted solution]

Hi  @attilajozsef712 ,

Create  a new table as below:

Table 2 =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            'Table',
            "Country", 'Table'[Country A],
            "Region", 'Table'[Region A],
            "Club", 'Table'[Club Seller],
            "Target", 'Table'[Target],
            "Transfer", 'Table'[Transfer Price]
        ),
        SELECTCOLUMNS (
            'Table',
            "Country", 'Table'[Country B],
            "Region", 'Table'[Region B],
            "Club", 'Table'[Club Buyer],
            "Target", 'Table'[Target],
            "Transfer", 'Table'[Transfer Price]
        )
    )
)

And you will see:

Then you could do any filters as you like.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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