Create column in table A with values from table B or C depending on condition

[Chocolate]

Hi
I have the following tables / columns:

Table A:
Key (based on Customer Number & Order Number, formatted as text)
Customer Number
Order Number
Other information

Table B:
Key (matching the Key from Table A)
Region B

Table C:
Customer Number
Region C

Both table B and C contain unique values and are related to Table A through the Key respectively the Customer Number.
What I like to do now is to create a new column in Table A which contains the following information:
If the Key from Table A is available in Table B, take Region B.
If the Key from Table A is not available in Table B, go to Table C and take Region C based on the Customer Number.
If error, leave blank.

I tried to solve it with the function LOOKUPVALUE. However, I don't manage to get it to work.

How would you solve it?
Any ideas are appreciated 🙂

Many thanks for your help!

[v-juanli-msft]

Hi @Chocolate 

Create three columns

RELATED FROM B = RELATED(TableB[RegionB])

RELATED FROM C = RELATED(TableC[RegionC])

Column_final = IF([RELATED FROM B]<>BLANK(),[RELATED FROM B],IF([RELATED FROM C]<>BLANK(),[RELATED FROM C],BLANK()))

Relationships are as below

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Chocolate]

Hi Maggie

Thank you so much for your help!

I started with creating column "RELATED FROM C". I have the same relationship as in your screenshot, however, I receive the following error message:
"The column 'TableC[RegionC]' either doesn't exist or doesn't have a relationship to any table available in the current context."

To me the error message makes no sense, as a relationship exists. I am also able to create a visual which includes both information from Table A and Table C.
I first thought that the issue may be that I do not have matching pairs for all the combinations. However, I tried to recreate the issue in a test file and there it does not cause an error.

I am working in DirectQuery in the original file. Does this make a difference?

Many thanks again.

[v-juanli-msft]

Hi @Chocolate 

Is the test file the same as your original file except the connection type "direct query"?

In your original file, the relationship between TableA and TableC is one -> many, right?

If so, it means that for each row in TableA you can have more than one row in TableC.

Therefore, you must use RELATEDTABLE instead of related, And since this is going to return more than one row , You should wrap it in an aggregation function(max,count,,ect).

If the relationship is many to many, please refer to a similar thread:

Table Relationship Doesn't Work

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.