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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Memorable Member
Memorable Member

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.