How to addcolumns(?) with lookupvalue() in DAX?

[gtutusaus]

Hi experts!

I am using INDEX&MATCH (or VLOOKUP) to bring into a table called bdActions some data from another table called bdContactos. Basically, I am grabbing the name and the surname in bdContacts matching the ContactID.

=INDEX(bdContactos[Name];MATCH([ContactID];bdContactos[ContactID];0))

Something quite common to do but risky if somebody deletes the formula in the table.

DAX is really new for me but I can see its potential designing the models and enhancing the data process. I see that that the calculations are included into the data model but how can make that field calculated by DAX visible in the table that the user is feeding with data?. I tried something like this but never worked:

=ADDCOLUMNS(bdActions;"Name3";LOOKUPVALUE(bdContactos[Name];bdContactos[ContactID];[ContactID]))

I googled a lot about this but nothing appears with this too DAX formulas in the same sentence. Any advice?

Many thanks
Gerónimo

[CheenuSing]

Hi @gtutusaus

I assume bdContactos table contains unique value of [ContactId]

Using the manage relationship under modelling tab,

 create a relationship between bdContactos and bdActions table on [ContactId] column from both the tables.

Then you do not need to write any DAX expression to get the names.

Try it out and let me know.

If it works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing 

[gtutusaus]

Thanks for answering!

As you suggested, both are related by ContactID. The relationship already existed. Today I tried with related() instead of loolupvalues() but didn't work neither.

I have contact details in a first table that I need to recall in the second table to the user. I am already doing this by index and match (or vlookup) in the second table so, technically I am repeating that data.

Maybe it doesn't make sense but I thought that using DAX I would be avoiding the chances that any accident, changes the current formulas in the spreadsheet table.

(By the way, I am using Power Query in Excel 2016)

Thanks again. Cheers,
Gerónimo

[v-yulgu-msft]

Hi @gtutusaus,

Based on my understanding, you want to add a new column into bdActions table, the values of this new column is fetched from the column [name] in bdContactos table, right?

In this scenario, to add a column using lookupvalue function, you only need to type below DAX formula into the blank textbox:

NewColumn=LOOKUPVALUE(bdContactos[Name],bdContactos[ContactID],bdActions[ContactID])

 

Best regards,
Yuliana Gu

[gtutusaus]

I totally get that (I have some others calculations and metrics developed already) but I was expecting to make a further step. How do I make that NewColumn visible in the table in the spreadsheet? Not in the pivot table but in the table that the user fill the data in.

Thanks

[v-yulgu-msft · Accepted solution]

Hi @gtutusaus,

I understand your requirement right now. But this is not a supported feature. Based on my test, the calculated column can only be displayed in pivot table.

Best regards,
Yuliana Gu

[gtutusaus]

Many thanks! It seemed to me like I was doing something impossible.

Cheers,

Gerónimo