Combining Columns in Different Tables

[RichardLinderma]

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?

[Anonymous · Accepted solution]

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.

[Ritaf1983]

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 .

[RichardLinderma]

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.

[saritasw]

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

[RichardLinderma]

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.

[saritasw]

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

[RichardLinderma]

Unfortunately, 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.

[saritasw]

@RichardLinderma  you mentioned - 

invoice_details[b_location_hierarchy_origin] → true_origin[a_location_hierarchy]

invoice_details[c_location_hierarchy_destination] → true_destination[d_location_hierarchy]

These are many-to-one relationships, pointing from invoice_details (many) to true_origin and true_destination (one), which is ideal for RELATED() as long as the relationship is active and correctly set up.

If for some reason the relationships are broken or not recognized, try LOOKUPVALUE() instead. This function doesn't rely on an existing relationship.

Combined_Cities = 
LOOKUPVALUE(
    true_origin[Origin_City_Name],
    true_origin[a_location_hierarchy],
    invoice_details[b_location_hierarchy_origin]
) 
& " / " &
LOOKUPVALUE(
    true_destination[Destination_City_Name],
    true_destination[d_location_hierarchy],
    invoice_details[c_location_hierarchy_destination]
)

This should work regardless of model issues, as long as your key values match exactly.

*****************************************************************************************************

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