I have two tables. srcActive and srcOpenPositions. srcActive TableMonthYear EEID auto_req_ID Hire_Date Employee_GroupSunday, January 01, 201722222Z1Friday, January 20, 2017HourlySunday, January 01, 201733333Y1Saturday, January 14, 2017ExemptWednesday, February 01, 201744444X1Friday, February 10, 2017Non-ExemptMonday, May 01, 201755555W1Friday, May 05, 2017InternThursday, June 01, 201766666V1Thursday, June 01, 2017ExemptSunday, October 01, 201777777U1Tuesday, October 10, 2017Non-ExemptFriday, December 01, 201788888T1Friday, December 01, 2017InternMonday, January 01, 201899999S1Tuesday, January 23, 2018ExemptSunday, April 01, 201812122R1Tuesday, April 17, 2018Non-ExemptTuesday, May 01, 201813133Q1Wednesday, May 30, 2018InternSunday, July 01, 201814144P1Saturday, July 07, 2018ExemptSunday, July 01, 201815155O1Monday, July 09, 2018Non-ExemptWednesday, August 01, 201816166N1Wednesday, August 08, 2018Exempt srcOpenPositionsMonthYear auto_req_ID Employee_Group Wednesday, February 01, 2017X1 Monday, May 01, 2017W1 Thursday, June 01, 2017V1 Sunday, October 01, 2017U1 Friday, December 01, 2017T1INMonday, January 01, 2018S1EXSunday, April 01, 2018R1NETuesday, May 01, 2018Q1INSunday, July 01, 2018P1EXSunday, July 01, 2018O1NEWednesday, August 01, 2018N1EXThursday, August 02, 2018M1NEWednesday, August 15, 2018L1INSaturday, September 01, 2018K1EX 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

don_writer

Helper II

8 years ago

Solved

DAX Pulling info from another table

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

Need Help

Anonymous

8 years ago

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

lookupvalue.pbix91 KB

4 Replies

Anonymous

Not applicable

8 years ago

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

lookupvalue.pbix91 KB

don_writer
Helper II
8 years ago
Anonymous
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
  Helper II
  8 years ago
  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

Forum Discussion

DAX Pulling info from another table

4 Replies

Related content

Error pulling data form another semantic model

Filtering a table with multiple conditions from multiple switches pulled from another table

Search then pull column data into another table

Pull data from one table to another without relationship

Pulling calculated measure from another Table