Forum Discussion
Combining Columns in Different Tables
Hi RichardLinderma ,
Thank you saritasw and Ritaf1983 for the quick response!
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.
Hi RichardLinderma ,
DAX in Power BI does not allow direct row-by-row references across different tables in a calculated column or measure unless there's a relationship between them.
MAX function returns the max value of that column. It’s an aggregation, not a row-by-row value. That’s why you’re seeing unexpected behavior.
If there's a relationship between the true_origin and true_destination tables , you can use the RELATED function in a calculated column.
CombinedCity = true_origin[Origin_City_Name] & " / " & RELATED(true_destination[Destination_City_Name])
However, If there's no relationship, you may need to create one or use LOOKUPVALUE instead.
***********************************************************************************************************************
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you,
Sarita
Hi, so true_origin and true_destination do not have a direct connection with each other, but they are both connected to another table called invoice_details. Hope this clears some stuff up.
Hi RichardLinderma ,
Thanks for the detailed description.
Your issue stems from trying to directly reference columns from unrelated tables in a calculated column or measure, which isn't allowed unless proper relationships are in place and used correctly.
Assuming you want to create a new column in the invoice_details table that combines the related Origin_City_Name and Destination_City_Name, here's how to do it using the RELATED function.Combined_Cities = RELATED(true_origin[Origin_City_Name]) & " / " & RELATED(true_destination[Destination_City_Name])RELATED() pulls values from a related table into the current row context. Perfect for what you're trying to do.
In this case, I won't suggest using CONCATENATE() because it requires both arguments to be in the same row context (i.e. from the same table or via relationships). DAX gets confused because it doesn’t know how to combine data from two unrelated row contexts without explicit relationships or context filtering.************************************************************************************************************
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you,
SaritaUnfortunately, this still hasn't worked. true_origin[Origin_City_Name] and true_destination[Destination_City_Name] are apparently not the right types of parameters for the RELATED function, and it says it can't find Origin_City_Name and Destination_City_Name when using the function anyway.
