Calculate a single column from 2 columns from different tables

[mhammo]

Hello All,

I may be missing something easy so please forgive. I have attached an example file of the "source data".

I want to take the Contact Name from the contacts table, the Company name from the company table and merge them both into a new single column in the Sales table, called Customer.

The end result I want is: CustomerID, Customer, Item, Amount Is this possible with DAX?

File: pbix

Mike

[MFelix]

Hi @mhammo,

Not really sure if I understand your question since the fields on your data based have different names.

But if ContactID = CustomerID then you just have to make a relationship between Contacts and Sales tables by this two fields and everything will be working as you need.

However since the contactID is smaller than the customerID list the name will be missing on some rows.

If you want to have asingle table that combine both them just add the following dax to create a new table:

Table =
UNION (
    SELECTCOLUMNS ( Company; "ID"; Company[Companyid]; "Name"; Company[Company] );
    SELECTCOLUMNS ( Contacts; "ID"; Contacts[Contactid]; "Name"; Contacts[Name] )
)

Then relate this table with your sales table and create the visual.

See attach PBIX with the changes.

Regards,

MFelix