DAX Pulling info from another table

[don_writer]

I have two tables. srcActive and srcOpenPositions. 

srcActive Table

MonthYear                                     EEID      auto_req_ID    Hire_Date                         Employee_Group

Sunday, January 01, 2017	22222	Z1	Friday, January 20, 2017	Hourly
Sunday, January 01, 2017	33333	Y1	Saturday, January 14, 2017	Exempt
Wednesday, February 01, 2017	44444	X1	Friday, February 10, 2017	Non-Exempt
Monday, May 01, 2017	55555	W1	Friday, May 05, 2017	Intern
Thursday, June 01, 2017	66666	V1	Thursday, June 01, 2017	Exempt
Sunday, October 01, 2017	77777	U1	Tuesday, October 10, 2017	Non-Exempt
Friday, December 01, 2017	88888	T1	Friday, December 01, 2017	Intern
Monday, January 01, 2018	99999	S1	Tuesday, January 23, 2018	Exempt
Sunday, April 01, 2018	12122	R1	Tuesday, April 17, 2018	Non-Exempt
Tuesday, May 01, 2018	13133	Q1	Wednesday, May 30, 2018	Intern
Sunday, July 01, 2018	14144	P1	Saturday, July 07, 2018	Exempt
Sunday, July 01, 2018	15155	O1	Monday, July 09, 2018	Non-Exempt
Wednesday, August 01, 2018	16166	N1	Wednesday, August 08, 2018	Exempt

srcOpenPositions

MonthYear                              auto_req_ID    Employee_Group

Wednesday, February 01, 2017	X1
Monday, May 01, 2017	W1
Thursday, June 01, 2017	V1
Sunday, October 01, 2017	U1
Friday, December 01, 2017	T1	IN
Monday, January 01, 2018	S1	EX
Sunday, April 01, 2018	R1	NE
Tuesday, May 01, 2018	Q1	IN
Sunday, July 01, 2018	P1	EX
Sunday, July 01, 2018	O1	NE
Wednesday, August 01, 2018	N1	EX
Thursday, August 02, 2018	M1	NE
Wednesday, August 15, 2018	L1	IN
Saturday, September 01, 2018	K1	EX

What I want to do is create a coumn that changes pull Employee group into the Open Postions table via Auto_req_ID. The two tables have some overlap in Auto_Req_ID but not all. If it doesn't exist I'd like to convert the abbreviation in the OpenPositions Employee_Category to be spelled out like in the Active table. Here is the code I wrote but I am uncertain what I am doing wrong.

EmployeeCategory = SUMMARIZE(
    srcOpenPositions,
    srcOpenPositions[auto_req_id],
    srcOpenPositions[employee_category],
    "Employee_Subgroup",
    SWITCH( TRUE(),
            srcOpenPositions[employee_category]="EX","Exempt",
                srcOpenPositions[employee_category]="NE","Non-Exempt",
                srcOpenPositions[employee_category]="PX","Project Employee Exempt",
                srcOpenPositions[employee_category]="PH","Project Employee Hourly",
                srcOpenPositions[employee_category]="IN","Intern",
                srcOpenPositions[employee_category]="HR","Hourly",
                srcOpenPositions[employee_category]="",LOOKUPVALUE(srcActive[employee_group],srcActive[Auto_Req_ID],srcOpenPositions[auto_req_id])
         ))

Your help is appreciated,

~Don

[v-yuezhe-msft · Accepted solution]

@don_writer,

Your code works well and returns correct result based on the sample data. What issue do you get?

EmployeeCategory = SUMMARIZE(
    srcOpenPositions,
    srcOpenPositions[MonthYear],
    srcOpenPositions[auto_req_id],
    srcOpenPositions[employee_category],
    "Employee_Subgroup",
    SWITCH( TRUE(),
            srcOpenPositions[employee_category]="EX","Exempt",
                srcOpenPositions[employee_category]="NE","Non-Exempt",
                srcOpenPositions[employee_category]="PX","Project Employee Exempt",
                srcOpenPositions[employee_category]="PH","Project Employee Hourly",
                srcOpenPositions[employee_category]="IN","Intern",
                srcOpenPositions[employee_category]="HR","Hourly",
                srcOpenPositions[employee_category]="",LOOKUPVALUE(srcActive[employee_group],srcActive[Auto_Req_ID],srcOpenPositions[auto_req_id])
         ))

Instead of creating a new table, you can directly create a new column in the srcOpenPositions table using DAX below. For more details, please check attached PBIX file.

Column = IF(srcOpenPositions[employee_category]="",LOOKUPVALUE(srcActive[employee_group],srcActive[Auto_Req_ID],srcOpenPositions[auto_req_id]),SWITCH(TRUE(),
            srcOpenPositions[employee_category]="EX","Exempt",
                srcOpenPositions[employee_category]="NE","Non-Exempt",
                srcOpenPositions[employee_category]="PX","Project Employee Exempt",
                srcOpenPositions[employee_category]="PH","Project Employee Hourly",
                srcOpenPositions[employee_category]="IN","Intern",
                srcOpenPositions[employee_category]="HR","Hourly"))

Regards,
Lydia

[don_writer]

@v-yuezhe-msft

Hi Lydia,

I'm not certain why but in my real dataset I get the error:

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

For both the table and column versions.

Yes the tables are bigger but otherwise I can't see any significant differences other than more fields. 😞

Thank you for your input,

~Don

[don_writer]

An update:
I realized that in the actual data there are multiple instances of each auto_req_id for that lookup value. I will figure out how to fix that and get back.

Thanks,

~Don

[v-yuezhe-msft]

@don_writer,

For the first DAX, please click "New Table" to apply formula. For the second DAX, please right-click you table and select "New Column" then apply the formula.

Regards,
Lydia