Relationship between 2 tables with lookup or contains

[Anonymous]

I have 2 tables

Table 1 has the technician name and the states that they cover. Many of them cover multiple states represented by the state abbreviation and a comma in between each state. 

Table 2 has the 50 states with the count of state.

I'm trying to create a power BI where when I click on a state in the map, it'll filter the table in the right if it contains the state abbreviation. Also, if I click the state in the table on the right, the bubbles from the map will show for those states ie. NM, TX for Alan.

Right now, when I click on California for instance, the list will show techs who cover only California.

[Thingsclump · Accepted solution]

Hi @Anonymous 

Please split table 1 in powerquery. You can split "states" column into "rows". Table 2 can stay as is. Create a relationship between states column in table 1 and Abb column in Table 2. 

Now create a measure as below

States name=CONCATENATEX('Table 1', 'Table 1'[states],",")

Create a matrix with rows as Primary contact name and measure as values.

This works when you select a single primary contact or multiple. Works for states selection as well. Map gets updated for selection that you make in Matrix.

Thanks

Thingsclump