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
vcb_001
Frequent Visitor

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

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

IDName
887ABC
121XYZ
990KLM
887null
887ABC
121null

 

Table Y has only distinct values and no null cells.

IDName
887ABC
121XYZ
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.

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

@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

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

1 REPLY 1
rubayatyasmin
Super User
Super User

@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

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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