Forum Discussion
Combining Columns in Different Tables
I have two text columns in separate tables, true_origin[Origin_City_Name] and true_destination[Destination_City_Name]. I want to create a new column/measure that combines the two into one field (i.e. reading like "London/New York"). However, using the CONCATENATE function, as well as an average &" / "& format, doesn't work as it says that it can't find the columns in question. I know this is common for non-calculated columns/measures, but I also tried using the MAX function and it just returns one value for each column entry in testing. How do I get around this?
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.
8 Replies
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,
SaritaHi, 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,
Sarita
Hi RichardLinderma
The solution depends on the model and the types of relationships—or even whether relationships exist at all—between the tables.
Therefore, to provide help, we need to see what you’re working with.
Please prepare a small PBIX example containing only the relevant columns, using the original model structure.
Save it to a public cloud service and share the link .So true_origin and true_destination are both connected to a table named invoice_details as follows:
[invoice_details[b_location_hierarchy_origin] <— true_origin[a_location_hierarchy]]
[invoice_details[c_location_hierarchy_destination] <— true_destination[d_location_hierarchy]]
I have also provided the following example table for how all six columns/measures look together:
Origin_City_Name a_location_hierarchy b_location_hierarchy_origin c_location_hierarchy_destination d_location_hierarchy Destination_City_Name Tokyo 6056 6056 7359 7359 Tokyo New York City 7417 7417 11372 11372 Paris Paris 11204 11204 2918 2918 New York City Sydney 2246 2246 4434 4434 Rio de Janeiro Cape Town 2938 2938 10616 10616 Cape Town Rio de Janeiro 4960 4960 1788 1788 Sydney London 7653 7653 9054 9054 Cairo Istanbul 6664 6664 3959 3959 Toronto Toronto 2256 2256 3941 3941 Bangkok Bangkok 11102 11102 8784 8784 Istanbul
Hope this can give you some ideas.
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.
