filter on two columns (or condition)

[stauffermi]

Hi, i have two tables looking like this:

Locations:

ID	Location Name
1	Location 1
2	Location 2
3	Location 3

Requests:

ID	name	Location 1	Location 2
1	Jon	1	3
2	Mike	2	1
3	Bob	1	2
4	Bill	3

Now I want to display a Filter in the Report, where the users can select a location. After selection, the Request table should be filtered and show only data where the selected location is in column "location 1" or in column "location 2".

Any ideas how to do this, because I can only create one active relationship? i struggle with this. thanks for your help!

[stauffermi]

Thank you very much for your help! I was able to solve it with the measure.

[ddpl]

@stauffermi 

First you need to create duplicate request table and then create join table as per below

then use single filter for both the table as per below

[amitchandak · Accepted solution]

@stauffermi , do not join two tables or create one more independent table

Then try measure like

measure =

var _tab = summarize(allselected(Table1), Table1[Location ID])

return

Calculate(Count(Table2[ID]), filter(Table2, Table[Location 1] in _tab  || Table[Location 2] in _tab  ) )

or

measure =

var _tab = summarize(allselected(Table1), Table1[Location ID])

return

Calculate(Count(Table2[ID]), filter(Table2, Table[Location 1] in _tab  || Table[Location 2] in _tab  ), values(Table2[ID]) )