Calculated column with values from another table

[JOSERB]

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,

[V-lianl-msft · Accepted solution]

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.

[amitchandak]

@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])

[JOSERB]

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...

[ibarrau]

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

[JOSERB]

HI @ibarrau 

In fact I do not want to do a "many to many" relationship. I think It should be a "one to many" being "one" Table2, which is the filter of Table1, but PBI only allows me to do a "many to many" relationship between both tables. 
The Accept button does not work.... 

[JOSERB]

In fact, If I tried only to match ID1 with ID2 with RELATED(TABLE2,[ID2]), I find an error message that says " there is no relationship or it does not exist betwen both tables in the current context...  and the relationship I have created is indeed  ID1 = ID2!
Definately I am lost...

[amitchandak]

@JOSERB , The reason I can think of it is not working is that there might me white space. Trim the ID column in both tables and try

https://community.powerbi.com/t5/Desktop/How-to-trim-space-from-Power-BI/td-p/194308

https://www.youtube.com/watch?v=AhEVzeG40ko

[ibarrau]

That is strange. If power bi is not letting you create one to many you might want to check your data because the one side could have a duplicated or blank value.

Has the previous measure worked? do you mean you can't accept the post as solution?

Regards,