Forum Discussion
Calculated column with values from another table
Hi all,
I need your help for a relatively simple topic, but I cannot make it work as I would like to.
The goal is to add a calculated column from a table 2 into table 1, which are already connected by ID.
It is a "various to various connection". The big issue is that ID in table 1 is in format "TEXT" and ID in table 2 is in format "INTEGER"
Here is the example :
| TABLE 1 | TABLE 1 | TABLE 2 | TABLE 2 | ||
| ID1 | CALCULATED.COL | ID2 | DATE | ||
| ABC | null | 123 | 2018 | ||
| CDE | null | 456 | 2019 | ||
| 123 | 2018 | 678 | 2017 | ||
| 123 | 2018 | ||||
| 123 | 2018 | ||||
| 456 | 2019 | ||||
| 456 | 2019 | ||||
| 678 | 2017 |
Hi have tried with related table or changing the format but it is not working. I got the message that I a comparing different values and to use Format or Value, but not working neither.
Any ideas?
Thanks in advance,
Hi JOSERB ,
Have you tried to change the ID column of Table 2 to the text category to create a relationship?
After I try to do this, I can get the expected result based on the sample data provided by you by using the related function.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
JOSERB , if you connected then with the correct data type, a column in Table 1
CALCULATED.COL = related('TABLE 2'[Date])Force correct data Type
CALCULATED.COL = maxx(filter('TABLE 2','TABLE 2'[ID1] = ('TABLE 2'[ID2]&"")), 'TABLE 2'[Date])Hi amitchandak,
Thanks for your help.
Unfortunately it is not working properly. Lots of IDs remain empty. I used the same formulas you gave me before the post, but I have same values repeted ni both tables and I guess that's why it is not working, but no idea about how to amend it...Hi. First thing to tell would be that you have to be carefull with many to many relationships. They can be a problem. Now, if you want data from Table 2 to Table 1 or Table 1 to Table 2 you have to aggregate the value because each value from one table knows a lot from the other (that's what many to many mean). Once you have the idea of what aggregation method use (max, min, count, sum, etc) now you can build something like this:
NewColumnTable1 = MAXX( RELATEDTABLE(Table2), Table2[Date] )The example is using MAXX but you can use any other aggregation method with X.
Regards
Hi JOSERB ,
Have you tried to change the ID column of Table 2 to the text category to create a relationship?
After I try to do this, I can get the expected result based on the sample data provided by you by using the related function.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
