Add New Column That Retrieves Value From Another Table

[dudeyates]

Hi all,

I have two tables. They are not linked by a relationship.

• Table 1 has billed timecard entries
• Table 2 is position data

The goal is to classify each labor hour entry as student, temporary, or permanent.

I've been trying to add a new column in Table 1 using DAX. When the Table 1 employee's work_date is between Table 2 start_date and end_date, retrieve the position_type.

scrubbed data:

I tried ADDCOLUMNS with LOOKUPVALUE using an Alberto Ferrari example. But DAX Studio gave error:
"A table of multiple values was supplied where a single value was expected."

I was assuming it would do the comparison row by row and pull the position_type. Instead, it appears LOOKUPVALUE wants a unique value, but an employee is listed multiple times and can have multiple entries for a work_date.

Also tried using FILTER:

New Table 1 Column position_type = 
CALCULATE ( 
	VALUES (
	    'Table 2'[position_type]
	),
	FILTER (
		'Table 2',
	    'Table 1'[emp_id] = 'Table 2'[emp_id]
	    && 'Table 1'[work_date] >= 'Table 2'[start_date]
	    && 'Table 1'[work_date] < 'Table 2'[end_date]
	)
)

But I get the same error as using LOOKUPVALUE

Is there a way to go row by row in Table 1, compare just that current row with Table 2, and retrieve the position_type when matches up?

Thanks for taking the time to look at this,
James

[FrankAT · Accepted solution]

Hi @dudeyates ,

you can do it like this:

position_type = 
VAR _Table =
    FILTER (
        Table2,
        Table1[emp_id] = Table2[emp_id]
            && Table1[work_date] >= Table2[start_date]
            && Table1[work_date] <= Table2[end_date]
    )
RETURN
    MINX ( _Table, Table2[position_type] )

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[dudeyates]

Thank You @FrankAT!  It works!

There is much happiness in Dudeville 🙂

[HotChilli]

Try and replace the VALUES with MIN so that you can try and get past the error which seems to be returning more than one entry for the same filter conditions.  I can't see (from the data shown) which conditions would allow that but let's start with that.

If you get past the error but a wrong result is in one of the rows, try and narrow in on that to debug.

Can you confirm that the datatypes are the same for each column that is compared in the filter i.e. date with date, int with int?