Forum Discussion
filter on two columns (or condition)
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 , 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]) )
3 Replies
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]) )
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
Thank you very much for your help! I was able to solve it with the measure.
