Complicated relationship in tables

[Abd_Kh]

Hello everyone, I have some data that contains school list and contractors, each school have 3 contractors (O&M, HVAC and HK) and I have set up the schools location on a map visualization (using latitude and longitude), my question is how to create a relationship between the contractors and the school so that when ever I click on a contract the map will only show the schools which is under that contract.

the school list:

Contract details:

My visualization:

the relationships (I cannot activite the 3 relationship since it is on the same table)

[Wilson_ · Accepted solution]

Hi Abd_Kh,

Unpivot your school list table, so that you have a contractor type column as well. For example, school number 100001 would look like:

I would also join the two tables together and bring the Value column into the school table as well. That seems to be the only additional piece of information stored separately.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.