How do I create a Calculated Column or Measure by relationship with multiple tables (text values)

[Anonymous]

Hi;

I need to create a calculated column or measure that will bring the PO Number from 4 other tables based on the Serial Number relationship between them. As you can see in the example below; table 1 serial number field matches with at least one Serial Number on tables 2, 3, 4 and 5. So I need this calc. column or measure to retrieve those values and populate them on Table 1. I tried using SUMX but it does not work with text string (values). It worked like a charm in a similar case where I needed to retrieve the currency USD value for each of the Serial Numbers.

Thanks in adavance for the help!

[VahidDM · Accepted solution]

Hi @Anonymous 

Can you Append those tables 2,3,4,5 to one table, then relate that with Table 1?and use the LOOKUPVALUE DAX

https://docs.microsoft.com/en-us/power-query/append-queries

Or use UNION code to join tables 2,3,4,5 and create one table,, then relate that with Table 1?and use use the LOOKUPVALUE DAX

https://docs.microsoft.com/en-us/dax/union-function-dax

Otherwise, you can use the LOOKUPVALUE DAX and IF statement code to find those PO Numbers.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

[Anonymous]

Thanks so much for the prompt reply!

Unfortunately, appending tables or using union code to create/consolidate tables is exactly what I am trying to avoid since each of those tables have 100's of 1000's of rows. The smallest table has at least 360K rows and the biggest has up to 2.1M rows. Besides, the PO Number is not the only value I need to retrieve and populate into Table 1; PO Number is just one of many, if resolved I will then use the same code / principle to retrieve all other values/columns.

Thanks again!... I will try your suggestion using Lookupvalue with IF Statements.

Cheers!