Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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.
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.
@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
Happy to help!
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....
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,
Happy to help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |