Power BI: Joining 2 tables using a calculated column

[Hewes_uk]

I have 2 tables which I would like to join in Power BI.

The first table is part of a large model to which I'm connecting. In that model is a table with a list of IDs.

Table 1

ID	Some Property	Another Property	Something else	etc
1	a	d	g	etc
2	b	e	h	etc
3	c	f	i	etc

I also have a Excel document (from MS Forms) which I'm importing into a second table.

Table 2

Date	identifier	answer 1	answer 2	etc
20241001	xy112	a	d	etc
20241002	pl225	b	e	etc
20241002	xy112	c	f	etc

I would like to add a calculated column into table 2 which would be an ID. Thereafter I could join table 1 and table 2 using that [id] field (one to one).

I have a number of rules which I can use to work out which record from table 1 is being referenced by a record in table 2 (some of which are based on table 1, some of which are based on other tables within the existing model).

Is there a way of calculating an [id] field in table 2, and then using that to join back to table 1?

Many thanks in advance.

[v-jianpeng-msft]

Hi, @Hewes_uk  

I wish you all the best. Previously Irwan provided a solution to help you solve the problem. Since I see that Irwan's proposed solution fulfills your needs, I would like to confirm if you have successfully solved this issue or if you need further help?
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

Best Regards

[Irwan · Accepted solution]

hello @Hewes_uk 

yes, it is possible to calculate a value from one table to another table but you need a comparable value between those two tables.

for example in your sample tables, i assumed it is a random data so i take 'Table 1'[Some Property] is same data as 'Table 2'[answer 1].

basically the DAX will compare the value from 'Table 1'[Some Property] and 'Table 2'[answer 1].

If any value matched, then it would return 'Table 1'[ID] in calculated column Table 2.

Hope this will help.

Thank you.