How to have lookup values in another table with condition?

[Anonymous]

Hi There,

I have two source of table which i would like to combine my data.

Table 1 contains information of the actual spend of the product code and the sourcing status. The principal name has an error for all code that has 'Single' as their sourcing status:

Code	Spend	Principal	Sourcing Status
1010	1000	Agent	Single
1013	2000	Agent	Single
1043	1400	Agent	Single
1238	300	Agent	Single
1269	2344	Principal A	Multiple
1269	3045	Principal D	Multiple
1138	450	Principal A	Multiple
1138	130	Principal B	Multiple
1123	450	Principal C	Multiple
1123	444	Principal D	Multiple

Table 2 has the correct principal information:

Code	Principal	Sourcing Status
1010	Principal A	Single
1013	Principal B	Single
1043	Principal C	Single
1238	Principal A	Single
1269	Principal A	Multiple
1269	Principal D	Multiple
1138	Principal A	Multiple
1138	Principal B	Multiple
1123	Principal C	Multiple
1123	Principal D	Multiple

How do i use Lookupvalue function to get the principal value for code that has "Single" status? Lookupvalue will return error if it lookup for code that has "Multiple" status as it will return multiple value where a single value is expected. 

Desired output:

Code	Spend	Principal	Correct Principal
1010	1000	Agent	Principal A
1013	2000	Agent	Principal B
1043	1400	Agent	Principal C
1238	300	Agent	Principal A
1269	2344	Principal A	Principal A
1269	3045	Principal D	Principal D
1138	450	Principal A	Principal A
1138	130	Principal B	Principal B
1123	450	Principal C	Principal C
1123	444	Principal D	Principal D

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To create a calculated column in Table 1.

Column = 
IF (
    'Table1'[Sourcing Status] = "Single",
    LOOKUPVALUE ( Table2[Principal], Table2[Code], Table1[Code] ),
    Table1[Principal]
)

[Anonymous]

hI @Anonymous 
Try the following measure

Measure =
VAR __complete = "Single"
RETURN
CALCULATE(
MAX( Table2[Principal] ),
CUSTOMERS[Sourcing Status] = __complete
)

 a little trick I learned this week

Thanks!

[Anonymous]

Hi @Anonymous ,

The solution you proposed only returns one principal from Table 2.

Now the  code with the "Multiple" status is also returning Principal C, whereas i originally wanted the previous correct principal to remain. 

Any ideas on how to solve this?