Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Pbiuserr
Post Prodigy
Post Prodigy

How to add new columns by custom table in dax

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]

1 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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

5 REPLIES 5
hnguy71
Super User
Super User

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]) 
)


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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
Super User
Super User

You could likely use LOOKUPVALUE for your Employee Name column. 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.