Here is what I would like to happen.
I have a filter in which I select a "Department" which is just a number between 1 and 115 and only a single value will be selected.
Then I want to go to 'Table1'[Department] and find the row that matches the previously selected value.
Then I want to find in another column but in the same table and row the value "Region".
Then I want to go to 'Table2'[Region] and find the matching region.
When the correct row is found I then need to look in the other columns.
Each region has between 1-6 "Neighbour regions". This means that after the first column[Region] there is 6 other columns called neighbour1, neighbour2, ..., neighbour6, which either has a value or is null.
I would then like to make a list that consists of the main region and its neighbour regions so that I then can use this list to filter a visulization to only show the data for these regions.
I would like all of this to be dynamic so that if I change the selected "Department" in the filter everything else happens automatically.
Any suggestions to have this can be implemented?
