Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JOSERB
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
V-lianl-msft
Community Support
Community Support

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
V-lianl-msft
Community Support
Community Support

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.

amitchandak
Super User
Super User

@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


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

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
Frequent Visitor

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

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.