Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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.
Solved! Go to 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
Proud to be a 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
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
117 | |
84 | |
49 | |
38 | |
28 |
User | Count |
---|---|
185 | |
73 | |
73 | |
50 | |
42 |