Create column based on columns in others tables

[RenaudF]

Hello,

I  have the following model: 

And I would like to create a column in All Items table which contains text in SAPBW_Items[ExtMatGroupName] if DALI_ProductFam[DALI ProductFamDescription] is empty. (All items are or in SAPBW_Items or in DALI_ProductFam but cannot be in both)

I managed to do it with the following DAX formula:

ProductFamily V2 = 
var _CodeItem = 'All Items'[CompanyCodeItem]
var _DALIItemSkey = LOOKUPVALUE(DALI_Items[DALI ProductFam],DALI_Items[Item_Skey_Bigint],_CodeItem)
var _DALIFamily = LOOKUPVALUE(DALI_ProductFam[DALI ProductFamDescription],DALI_ProductFam[ProductFam],_DALIItemSkey)
var _SAPFamily = LOOKUPVALUE(SAPBW_Items[ExtMatGroupName],SAPBW_Items[Item_Skey_Bigint],_CodeItem)
Return
COALESCE(_DALIFamily,_SAPFamily)

But as the dataset is really huge, the calculation time is a little bit slow... Is a way to improve it? PS: 'All Items'[CompanyCodeItem], DALI_Items[DALI ProductFam] and SAPBW_Items[ExtMatGroupName] are columns of strings.

[FreemanZ · Accepted solution]

If there is no misunderstanding, a new column in All Items table with the code below shall work:

 

Column = REALTED( SAPBW_Items[ExtMatGroupName]) & RELATED( DALI_ProductFam[DALI ProductFamDescription])

[RenaudF]

Fantastic. Thanks!!!

[amitchandak]

@RenaudF , If the direction of copy is 1- many use related.

if direction of Copy relatedtable

example

realtedtable(Table2, Table2[Value])

related(Table1[Value])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Ideally if the performance is slow, you can consider merge in power query, or better move the calculation to source

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[RenaudF]

Thanks for links and idea.

Unfortunately the tables are in DirectQuery and I'm not able to use PowerQuery for them.