One columnn to two column relationship

[rjtedge]

Hi. I have two tables. The first is just a list of names. The second had multiple columns with project information. Each project has a unique idb and then two columns for primary and secondary contacts. Like below

Table 1:

Name

Joe

Jane

Bob

Roger

Table 2:

Id | Primary | secondary

1  | Joe        | Jane

2  | Bob       | null

3  | Jane      | Joe

I want to set up two visuals.

The first as a table or matrix showing the number of primary and secondary projects each person is the contact for. 

Something like:

NAME | PRIMARY | SECONDARY

Joe      | 1              | 2

Bob     | 1              | 0

I then want a second visual with a table of project details that gets filtered when I click on the name in the first visual to show all projects that the person is either primary or secondary contact. And when I click on the number under primary or secondary number the second visual just shows the projects that the person is either just primary or just secondary contact. 

I think I am have problems because I am running across two separate columns in the second table. I tried to set up a relationship between the first table and the second table for both primary and secondary, but it won't let me. 

Any ideas? 

[CNENFRNL · Accepted solution]

Hi, @rjtedge , when you reshape your second table like this, all issues are gone with the wind. It's only a matter of clicks in Power Query.

You may want to refer to the attached file for details.

[amitchandak]

@rjtedge , I am getting your calculation.

But Assuming the first one is the master table. Join it with both Primary and secondary. One join will be active and another one will be inactive. use userelation to select the join in a measure

refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...