How to add new columns by custom table in dax

[Pbiuserr]

Hello,

I've created a table which is a unique ID value from two tables using

NewEmpTable = 

DISTINCT ( 
UNION (
            DISTINCT(Table1[ID]),

            DISTINCT(Table2[ID]) ) )
and now I'd like to bring a name from Table3 to that ID and the manager (they also have ID there in the table)

I know it should start with ADDCOLUMNS, but then what?

ADDCOLUMNS (

DISTINCT ( 
UNION (
            DISTINCT(Table1[ID]),

            DISTINCT(Table2[ID]) ) ),
"Name", CALCULATE( MAX ( Table3[EmpName]) )
gives bad result. Can't do something like NewEmpTable[ID] = Table3[ID]

[hnguy71]

Hi @Pbiuserr ,

As long as you can somehow identify the same user in Table 3 then that's possible. I'm guessing Table3 has an ID field too...Check to see if this works:

VAR _DistinctValues = DISTINCT(UNION(DISTINCT(Table1[ID]),DISTINCT(Table2[ID])))

VAR _AddColumns = 
ADDCOLUMNS( _DistinctValues, 
    "Name", CALCULATE( MAX (Table3[EmpName]), [ID] = Table3[ID]) 
)

[Pbiuserr]

I get an error that: "Column 'ID' cannot be found or may not be used in this expression. Weired because when I typed [I..] it highlighted [ID] ad a possibility.

[jgeddes]

You could likely use LOOKUPVALUE for your Employee Name column. 

https://learn.microsoft.com/en-us/dax/lookupvalue-function-dax#syntax 

[Pbiuserr]

But I can't use already argument NewEmpTable[ID] in that, can I? so whats the column to lookvup value to?

[jgeddes · Accepted solution]

Something like this might work for you...

combinedIDTable =

var _vTable = 
ADDCOLUMNS(
    DISTINCT(
        UNION(
            DISTINCT(idTable1[ID]),
            DISTINCT(idTable2[ID])
        )
    ),
    "_name",
    LOOKUPVALUE(employeeTable3[Name], employeeTable3[ID], [ID])
)
return
_vTable