Forum Discussion
Filter two columns with an "or" condition
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 |
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.
3 Replies
niteshtrehan89Helper III
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.This worked like a charm, thanks!
- amitchandak
Super User
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))
