Filter two columns with an "or" condition

[MAAMI]

I have created the table below, as an example of a part of my query. My query is a combination of several reports / excel-sheets, which include transactions with a sender name and a receiver name.

I want to create a slicer filter showing all sender+receiver locations, where if I in example choose to filter by "New York", it will show all rows where "New York" is mentioned either as sender or receiver. So in this example, it should show row numbers: 1, 2 and 8. How can I develop such a slicer filter?

Row number	Report number	Sender	Receiver
1	1	New York	Berlin
2	1	Beijing	New York
3	1	Paris	Berlin
4	2	Los Angeles	Madrid
5	2	Los Angeles	Berlin
6	3	Tokyo	Madrid
7	4	Dubai	Copenhagen
8	4	New York	Beijing
9	4	Berlin	Paris

[niteshtrehan89 · Accepted solution]

Hi Maami,

First step is to create a calculated column in the table to combine the sender and receiver values.

SenderReceiver = COMBINEVALUES ( "|", Slicer[Sender], Slicer[Receiver] )

Then create the table with the below measure.

slicer test = DISTINCT(UNION(SELECTCOLUMNS(Slicer,"selection",Slicer[Sender],"both",Slicer[SenderReceiver]),

SELECTCOLUMNS(Slicer,"selection",Slicer[Receiver],"both",Slicer[SenderReceiver])))

Once the table is created, create a many to many cardinality and single filter direction between the table and slicer test table on senderreceiver and both columns.

[MAAMI]

This worked like a charm, thanks!

[amitchandak]

create a slicer that did not join to this table

And use this measure in you table of matrix

measure =
var _max = maxx(Slicer,Slicer[city]) // values(Slicer[city])
return
calculate(countrows(table),filter(table,table[Sender]=_max || table[Receiver]=_max))