Filter Table in special way based on column from another table

[Medo1110]

Hi!

Im struggling to create a table which is filtered by value selected from another table:

I have 2 Tables: Customers and Coffeeshops. 

Customer	Coffeeshop ID	Reciept
A	1	$                    20,00
A	2	$                    30,00
A	3	$                    10,00
B	1	$                    15,00
B	4	$                    30,00
C	1	$                    10,00
D	9	$                    20,00
D	10	$                    20,00
E	1	$                    30,00
E	4	$                    50,00
E	5	$                    20,00
F	1	$                    10,00
H	2	$                      4,00
H	3	$                    25,00
H	4	$                    12,00

Customer Table gives you an overview which customer ordered in which coffeeshop 

CofeeshopID	Coffeeshop Name
1	Atlanta CS
2	Los Angeles CS
3	Seattle CS
4	Barcelona CS
5	Madrid CS
6	London CS
7	Berlin CS
8	Amsterdam CS
9	Paris CS
10	Vienna CS

Coffeeshop Table is just an overview of all Coffeeshops.

What i want to achieve is: I want to select a coffeeshop: For example "Atlanta CS" . And my table should check if customer ordered in this coffeeshop AND in some another, and then give all customers IDs and Cofeeshop names where it was ordered except the one coffeeshop selected. Meaning i want to achieve something like this: 

Because Customer A ordered aswell as in Atlanta and in other 2, customer B in Atlanta and Barcelora etc. 

I thought i could achieve this by editing interactions: 

 Meaning I would filter customers that were  in Atlanta Coffeeshop and then i would pass only those customers to my Table. But it seems this doesnt work because if you select all customers, Table will also have Customer H for example. Plus im not sure how to exclude selected Coffeeshop after.

If you have any ideas i would appreciate any help! 
Thanks a lot in advance!

[Medo1110]

Thanks a lot! This solution works exactly as needed

[speedramps · Accepted solution]

Click here to download solution

Download PBIX 

How it works ...

Create a related and unrelated copy of shop table.
Use one to pick (slice) and the other to display.
Otherwise it is complicated to slice a shop and display the other shops ! 

Create a measure

Is Valid Customer = 
VAR pickshop = SELECTEDVALUE('Shop picklist'[CofeeshopID])    -- getspicked shop id
VAR customers =                                               -- get list of customers who visted picked shop    
CALCULATETABLE(
    VALUES(Sales[Customer]),
    ALL(Sales),
    Sales[Coffeeshop ID] = pickshop)
VAR thisshop = SELECTEDVALUE(Sales[Coffeeshop ID])             -- get shop being output
VAR thiscustomer= SELECTEDVALUE(Sales[Customer])               -- get customer being output
RETURN
SWITCH(TRUE(),                                                 
thisshop = pickshop, "N",                                       -- ignore picked shop
thiscustomer in customers, "Y"                                  -- flag customers who are in the list
)

Fiter you reports using the measure

Pay great care to use the correct table and field names.
iI you use the shop or the customer from the wrong table then the solutoon wont work.
Download my example and copy the code.

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers and you are receiving expert free help.
This solution works and does exactly what you asked.
So please be polite and quickly click the [accept as solution] and thumbs up and the button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.