cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 1TABLE 1 TABLE 2TABLE 2 
ID1CALCULATED.COL ID2DATE 
ABCnull 1232018 
CDEnull 4562019 
1232018 6782017 
1232018    
1232018    
4562019    
4562019    
6782017    


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,

 



1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Calculated column with values from another table

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.

test_Calculated column with values from another table1.PNGtest_Calculated column with values from another table2.PNGtest_Calculated column with values from another table3.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: Calculated column with values from another table

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Calculated column with values from another table

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

Highlighted
Super User II
Super User II

Re: Calculated column with values from another table

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



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


Highlighted
Frequent Visitor

Re: Calculated column with values from another table

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

Highlighted
Super User II
Super User II

Re: Calculated column with values from another table

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,



If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog


Highlighted
Frequent Visitor

Re: Calculated column with values from another table

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

Highlighted
Super User IV
Super User IV

Re: Calculated column with values from another table

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Microsoft
Microsoft

Re: Calculated column with values from another table

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.

test_Calculated column with values from another table1.PNGtest_Calculated column with values from another table2.PNGtest_Calculated column with values from another table3.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors