Creating a column which returns the value based on the condition on a column from different table

[vcb_001]

Hi everyone,

I would like to create a DAX formula that creates a new column in "Data View", or in Power Query using M. The example I provide in the below has 3 distinct values but, the exact table I'm working with has 508 distinct values, so using a conditional column that has more than 500 if clauses are quite problematic.

Therefore, I need your help to create the column based on the below logic using DAX or M language if it is applicable.

I have two tables, X and Y. Table X has values like (including null values in the second column, and this is the main reason why I need to create a new custom column which I can use instead of this non-completed column):

ID	Name
887	ABC
121	XYZ
990	KLM
887	null
887	ABC
121	null

Table Y has only distinct values and no null cells.

ID	Name
887	ABC
121	XYZ
990	KLM

What I would like to do is creating a new custom column using DAX or M which retrieves the names based on the condition between the ID column in the table X and table Y.

Looking forward to having your support.

Thanks in advance.

[rubayatyasmin · Accepted solution]

@Hi, @vcb_001 

at first create a relationship between X and Y table. then use this DAX to create the new column in X table,

NewColumn =
CALCULATE(
MAX('Y'[Name]),
FILTER(
'Y',
'Y'[ID] = 'X'[ID]
)
)

also, just a suggestation can you just remove nulls from the X table? As the values look same instead of creating new cols