Joining to lookup table where join is conditional on two columns

[andrewpirie]

I'm trying to add the name of the employee who created an entry in our Sales Order dataset in PowerBI. The relevant columns are as below:

SALESORDER
...
SALESPERSON_ID VARCHAR(20)

EMPLOYEE
OLD_FORMAT_EMPLOYEE_ID VARCHAR(20)
NEW_FORMAT_EMPLOYEE_ID INT
DISPLAYNAME VARCHAR(200)
...

In SQL I would do this by the below:

SELECT SALESORDER..., EMPLOYEES.DISPLAYNAME
FROM SALESORDER
   LEFT JOIN EMPLOYEE
      ON SALESORDER.SALESPERSON_ID = CAST(EMPLOYEE.NEW_FORMAT_EMPLOYEE_ID AS VARCHAR(20))
         OR SALESORDER.SALESPERSON_ID = EMPLOYEE.OLD_FORMAT_EMPLOYEE_ID

To do this in PowerBI I could either use the above, which feels like a cheat (I'm trying to become competent in PowerBI), or add a column with a DAX statement like below (which gives an error comparing Text and Number, which confuses me because I've changed the data type on 'Employee'[NEW_FORMAT_EMPLOYEE_ID] to Text in the Advanced Editor)

IF(
   IFERROR(VALUE('Sales Order'[SALESPERSON_ID]), -1) = -1,
   LOOKUPVALUE(
      'Employee'[DISPLAYNAME],
      'Employee'[OLD_FORMAT_EMPLOYEE_ID],
      'Sales Order'[SALESPERSON_ID]
   ),
   LOOKUPVALUE(
      'Employee'[DISPLAYNAME],
      'Employee'[NEW_FORMAT_EMPLOYEE_ID],
      'Sales Order'[SALESPERSON_ID]
   )
)

I'd appreciate any assistance getting this join/lookup to work in PowerBI.

Thanks,
Andrew

[v-yulgu-msft · Accepted solution]

Hi, @andrewpirie

First make sure that the data type of columns OLD_FORMAT_EMPLOYEE_ID and NEW_FORMAT_EMPLOYEE_ID in table EMPLOYEE are both text.

Add a calculated column in table SALESORDER:

Name = IF(
LOOKUPVALUE(EMPLOYEE[DISPLAYNAME],EMPLOYEE[NEW_FORMAT_EMPLOYEE_ID],SALESORDER[SALESPERSON_ID])= "",
LOOKUPVALUE(EMPLOYEE[DISPLAYNAME],EMPLOYEE[OLD_FORMAT_EMPLOYEE_ID],SALESORDER[SALESPERSON_ID]),
LOOKUPVALUE(EMPLOYEE[DISPLAYNAME],EMPLOYEE[NEW_FORMAT_EMPLOYEE_ID],SALESORDER[SALESPERSON_ID])
)

Best regards,
Yuliana Gu

[andrewpirie]

Thanks Yuliana_Gu, that's a brilliant solution! 

Andrew

[GilbertQ]

What about the option of using the Merge Queires option in the Query Editor?

That will give you all the required options as shown below.

[ankitpatira]

@andrewpirie Make sure data type on both the columns is same ie salesperson_id and new_format_employee_id.

[andrewpirie]

@ankitpatira They're both set to text in PowerBI