How to create a relationship to 2 columns in a table

[enterprised1]

Hello!

I have a table that contains task metrics and a 2nd table that contains staff information.  The issue i'm having in creating relationships between the 2 tables is that most tasks require 2 staff memebers and i'm not able to create 2 active relationships.

i.e.

Task Table             Staff Info Table

TaskID                   StaffID

Date                      Name

Staff1                    City

Staff2                    Shift

I'm trying to create the relationship between Staff1 & Staff2 in the Task Table to the Name column in the Staff Info Table so I can pull metrics for all staff on a particular shift in a given city.  I currently have a relationship between Staff1 and Name, which only returns half of the applicable staff members.  

How can i go about adding people listed in Staff2 to the metrics?

Thank You in advance!

[v-eqin-msft]

Hi @enterprised1 ,

I selected Staff1 and Staff2 columns and then clicked unpivot. The data table will be changed like this:

Then created relationship between Staff table and Tasks2 table.

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Greg_Deckler]

@enterprised1 I really think you have 2, possibly 3, options. 

1. Unpivot your Staff columns so that you end up with a single column with all of your Staff values and then form your relationship on that column (you may have to implement a bridge table)
2. Form the relationship on both columns and use USERELATIONSHIP in your measure(s)
3. Some other method of combining data using a measure, not sure what that may be, need sample data.

Cannot stress the importance of sample data and expected results. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[enterprised1]

Thanks for the tips on adding sample data, @Greg_Deckler .   

Here's what I'm hoping:

Right now, since the Staff table is only related to the Staff1 column in the Tasks table, when I filter for a specific City/Shift, I only get the person listed in Staff1.  I'd like to see both Staff1 and Staff2 listed in the Name filter in the bottom row in the screenshot below.

The top row comes from the Tasks table, and the bottom row comes from the Staff table.  

PBIX file here

[Greg_Deckler]

@enterprised1 - Not sure but check attached PBIX below sig. I unpivoted your Staff1 and Staff2 columns. Might have to do something about your Duration calculation though.

[enterprised1]

@Greg_Deckler Thanks for the pbix.  Would I have to create a different Relationship after unpivoting?  I'm still only seeing E listed in the Name field, whereas I'm expecting to see E & A

[Greg_Deckler]

@enterprised1 - Well, your Staff table only lists E as being in Shift 2 in Denver, correct? That's why you only see E. The more I look at this, it seems like you need to disconnect those 2 tables and use a Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

Alternatively, you need to somehow merge/join those tables into a single table that includes both of the staff people for Denver, Shift 2? It's like you need a Shift column in your Tasks table or something or, I don't know. But the problem is that you only have E listed as being in Shift 2 in Denver and not both E and the other one.

[enterprised1]

@Greg_Deckler Correct.  And that's why I originally thought creating the relationship to both Staff1 & Staff2 would be the way to go.  Right now, when I use the Clustered Column Chart to chart Staff performance, only part of their completed tasks are being accounted for (since staff members can be listed in either Staff1 or Staff2).  I was hoping there was a way to get all of the tasks accounted for for each staff member, regardless of if they were listed in Staff1 or Staff2.

[Greg_Deckler]

@enterprised1 - The easiest way to fix this is to make sure that all of the people that worked each shift are listed in the Staff table so you would have a row for E in Shift 2, Denver and a row for the other employee as well.

[enterprised1]

@Greg_Deckler That's how things are set up - Every staff member is listed in the Staff table along with their City and Shift.  The issue is that a staff member can be listed in either Staff1 or Staff2  in the Tasks table so I'm only getting a partial view of their overall performance.  

[Greg_Deckler]

@enterprised1 - There is probably a table "blow out" solution to this, I'm just too tired to get to it at the moment, it's after 1AM here and I need to get to bed. 😞

[enterprised1]

Lol no worries at all.  I appreciate you taking the time to walk me though this!  The unpivoting you did in the pbix gives me the correct duration (39 minutes) so that at least gets me closer to what I'm looking for.  I just need the partner name to show up.

At any rate, have a great night!

E listed as Staff1, partnered with A (line 13) - Duration=45

E listed as Staff2, partnered with A (line 5) - Duration=33

Average time = 39 minutes

[enterprised1]

Wow! You guys are quick!  I'm still a novice not just with PowerBI, but with data modeling in general so this will take me a while to understand.  I'm going to attempt to implement your suggestions and will report back with the results.  Thank You all!

[pranit828]

Hi @enterprised1 

I believe you need the task table to keep unique rows per task ID.

Then you might need to use the LOOKUPVALUE twice in two columns for both Staff1 and Staff2 as 

Staff1 City = LOOKUPVALUE('Staff Info Table'[City], 'Task Table'[Staff1], 'Staff Info Table'[StaffID])

Staff2 City = LOOKUPVALUE('Staff Info Table'[City], 'Task Table'[Staff2], 'Staff Info Table'[StaffID])

Staff1 Shift = LOOKUPVALUE('Staff Info Table'[Shift], 'Task Table'[Staff1], 'Staff Info Table'[StaffID])

Staff2 Shift = LOOKUPVALUE('Staff Info Table'[Shift], 'Task Table'[Staff2], 'Staff Info Table'[StaffID])

[amitchandak]

@enterprised1 , Not very clear. You can have one active and one inactive relation. Or need to have to copy of the table that depends on the need. So it like a game Team vs Team, you need 2 copies. If it like I will analyze by This date vs That date, you need one copy with one active and one inactive relation. With userelation you can use one of them as per need

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...

Or create one more copy of the

Staff 2=  Staff

[Ashish_Mathur]

Hi,

In the Task Table, you should select TaskID and Date and then "Unpivot the other columns".  Now create a relationship from the Value column to the Name column.

Hope this helps.