Filter an unrelated table with one of two values in a slicer, including output where both match?

[PieMashandGravy]

Hi - newbie here, having some partial success with a DAX issue but need some help getting over the line. 

I've changed and simplified my data to be able to post it here. I have a 'Stores' table, listing different types of stores in two cities (London and Manchester), which needs to be filtered by an unrelated slicer (for conditonal formatting reasons). The unrelated slicer table, 'Cities',  contains the two cities. 

Stores = {("London","Barber"),("London","Newsagent"),("London","Outdoor Store"),("London","Boutique"),("London","Dry Cleaner"),("London","Convenience Store"),("London","Chippy"),("London","Pawnbroker"),("London","Jeweller"),("Manchester","Barber"),("Manchester","Newsagent"),("Manchester","Vape Store"),("Manchester","Dry Cleaner"),("Manchester","Convenience Store"),("Manchester","Upholsterer"),("Manchester","Opticians"),("Manchester","Jeweller"),("Manchester","Chippy"),("Manchester","Kebab Shop")}

Cities = {("London"),("Manchester")}

I am trying to make the filter on the table work by putting a 0/1 condition in, off the back of a measure, which my intention is to return a 1 for a match on the city, and to pull back all stores with a 1. This measure then goes into the visual-level filter for the list of stores.

I can make it work with a super-simple measure comparing the MAX(Stores[City]) and the SELECTEDVALUE of the Cities slicer;

StoreList =

    IF (

        MAX(Stores[City]) = SELECTEDVALUE(Cities[City]),

        1,

        0

    )

However, the MAX component is tripping me up returning everything for Manchester, but only returning the London stores where they don't exist in Manchester. What I am looking to do is to adapt my DAX so that it returns a 1 for all rows where there is a match on the city, irrespective of whether there's a match on the other city or not, which I believe involves circumventing the use of aggregation on the Stores table to do the check.

Massive thanks in advance for any suggestions - I've tried all sorts of things (IN, CONTAINS, FIND, FILTER etc) but I cannot make it work.

[tamerj1]

Hi @PieMashandGravy 

please caleify the following 

"What I am looking to do is to adapt my DAX so that it returns a 1 for all rows where there is a match on the city, irrespective of whether there's a match on the other city or not"

[PieMashandGravy]

Hi @tamerj1 

So basically what I mean is, I want the measure to retrun a 1 for all values where the City matches, and not just the MAX. For example, in my above data, London has a Convenience Store, a Jeweller and a Dry Cleaner, which I think aren't being returned as 1 because Manchester also has them, and Manchester is the MAX value. What I am looking for is for Convenience Store, Jeweller and Dry Cleaner to be returned as 1 for both London and Manchester.

This all comes down to how I am writing the DAX in the Measure - I am using MAX because that's how I learnt to create measures which return values that can be filtered on. I think my question reduces down to how to create this measure differently, so that it does what SQL would do with SELECT * FROM Stores WHERE City = 'London'. 

Hope that makes sense. 

[tamerj1 · Accepted solution]

@PieMashandGravy 
Please try

StoreList =
INT (
    NOT ISEMPTY ( FILTER ( Stores, Stores[City] IN VALUES ( Cities[City] ) ) )
)

[PieMashandGravy]

Thank you @tamerj1, you are a legend! That works like a charm. I have no idea how long I would have been trying to figure that out for myself - my massive gratitude!