How to use DAX LOOKUP when tables do not share unique key?

[nick-evans]

I'm working with 2 tables from different sources.

My Employee table has 1 employee per row, with a unique ID per row. Ideally I would use this ID value to associate other tables to this one, but because my second table is coming from another source, it doesn't know that ID.

My Trainings table has 'Courses' and 'Name'. 

Employee[Name] = Training[UserName].

My issue is that each row within my Trainings table, is a unique entry, but not based on name.

A single user may appear numerous times (each entry indicating they've taken a different training).

If I try to make a relationship between Employees[Name] and Trainings[UserName] it gives an error:

You can't create a relationship between these two columns because one of the columns must have unique values.

It seems like LOOKUP is what I need in this scenario, but I'm struggling to make it work.

I tried going through this guide that is recommended on all the LOOKUP posts I have found, but the sample data it has, makes the 'State' unique in both tables, so I'm confused how to make it work in my scenario (where I lack a common unique key).

I believe what I need here, is to LOOKUP the Trainings[UserName] value in the Employees[Name] column, and return the corresponding Employees[StartDate].

I tried creating a new column within the Trainings table like this:

UserStartDate = LOOKUPVALUE(Employees[StartDate],Employees[Name],Training[UserName])

(In my head this should mean my first row in Trainings would look up 'Jim' in the Employees[Name] table, and return '7/4/2016' as the value.)

I get an error though: 

A table of multiple values was supplied where a single value was expected.

It seems like this is what LOOKUP is for - but I'm not sure how to do it based on what data my tables have available to them.

[Anonymous]

Thanks for posting the question, I run into the same issue. Looking forward to following the suggested solutions here. 

[TomMartens]

Hey,

My assumption for the problem you are facing is the following, there are more people called Jim in your employees table, basically this is no problem because you have the id column. For this reason you get the error message "... because one of the columns must have unique values".

This assumption seems to be true, because pulling the StartDate into you Trainings table using LookupValue also returns an error. This happen if there are multiple rows satisfying the condition "Jim" but would return different values for the Lookup column StartDate (here you will find the officil documentation for LOOKUPVALUE: https://msdn.microsoft.com/en-us/library/gg492170.aspx).

So you first have to define a business rule how to "properly" join both tables, it seems that using just UserName is not sufficient for your dataset.

Regards

Tom

[nick-evans]

What you're saying makes total sense, however in my actual data, we use First & Last name within the Employees[Names] & Trainings[UserName] columns. 

I just compiled a table of Employees[Name] & Count(Employees[Name]), and there is no count > 1. 

To me this should indicate that every Employees[Name] is unique, would you agree?

Trainings[UserName] will definitely have repeat items in it though, because each user, can take multiple courses.

Working under the assumption that Employees[Name] is unique, shouldn't I be able to LOOKUP Trainings[UserName], and return the corresponding Employees[StartDate]?

My hope was that LOOKUP would function like VLOOKUP in excel.

This is the behavior I would use in Excel and VLOOKUP to do what I am trying to replicate in Power BI with LOOKUP.

It looks at the current row for Trainings[UserName], looksup the corresponding value in Employees[Name], and returns Employees[StartDate].

So I guess, there's 2 questions there:

1. Do I understand the intended use of LOOKUP correctly?

2. Does my test with Count(Employees[UserName]) seem reasonable enough to verify no duplicate values?

EDIT:

I exported a list of 10 of Employees[Names], and then duplicated one of the names for a test.

When I built a new table of Employees[Names] & Count(Employees[Names]) it reflected that name has a count of 2 now.

I think that means my results when performing this test on the whole of my data should mean Employees[Name] has no duplicates.

[TomMartens]

Hey,

regarding your last questions, 

• yes, that's the intended use of LOOKUPVALUE
• seems reasonable

To verify that there are no duplicates in your employee table, create the following two measures

1st measure

No Rows Employees =
COUNTROWS('employees')

2nd measure

No of Distinct Names =
DISTINCTCOUNT('employees'[Name])

Put just these two measures to a table visual, nothing else, make sure there are no slicer selections, or page report level filter or anything that filters the rows of the employees table.

Both values have to match.

If they match, I have no idea what is going on, and it would be helpful if you can create sample data that allows to recreate the issue.

Regards

Tom

[nick-evans]

Well drats - looks like we've got duplicates 😞

So follow up question then (since I now understand at least one of my problems).

Assuming I get to the point that at least one of the columns is entirely unique, should the Relationship designer be capable of setting Employees[Name] to Trainings[UserName], and would my DAX formula have been right?

[TomMartens]

Hey,

yes, from the point when you have unique values in 'Employees'[Name] you can define a relationship between 'Employees'[Name] (on the one-side) and 'Trainings'[Name] (on the many-side).

As far as I understand, it has to be 'Employess'[Name] that has to become unique due to the nature of the Trainings table.

If the above is set up you can use

RELATED('Employees'[StartDate])

to create a calculated column in Trainings table to pull the StartDate in.

From a performance point, using RELATED(...) will execute much faster in comparison to LOOKUPVALUE(), this is due to RELATED(...) is able to leverage the relationship between both tables.

Regards

Tom

[Ashish_Mathur]

Hi @nick-evans,

If the ID column in the Employees table are all unique, then create a relationship from the UserName column in the Trainings table to the Name colum in the Employees table and the following two calculated columns in the Trainings table would work absolutely fine

=RELATED(Employees[StartDate])

=LOOKUPVALUE(Employees[StartDate],Employees[Name],[UserName])

Infact the LOOKVALUE function does not even require a relationship to be created.

Hope this helps.