Forum Discussion
Shared value from two different columns put into one
Hello I am combining notes from two different data sources and want to group rows based on a shared ID to combine the notes from these sources. In one table it is called 'objectid' and in the other it is 'regardingobjectid'. Is it possible for me to group by the shared value that each of these columns have in both the id fields shown below to combine the notes from each?
Create a bridge table that unions both ID columns:
Combined IDs =
DISTINCT(
UNION(
SELECTCOLUMNS(Table1, "ID", Table1[objectid]),
SELECTCOLUMNS(Table2, "ID", Table2[regardingobjectid])
)
)Relate both original tables to this bridge table. Then create a measure that combines notes from both tables using the shared ID relationship.
If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande
5 Replies
Create a bridge table that unions both ID columns:
Combined IDs =
DISTINCT(
UNION(
SELECTCOLUMNS(Table1, "ID", Table1[objectid]),
SELECTCOLUMNS(Table2, "ID", Table2[regardingobjectid])
)
)Relate both original tables to this bridge table. Then create a measure that combines notes from both tables using the shared ID relationship.
If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeHi, do I create the measure in the bridge table and which function should I use to create the measure?
Hi docs
You don’t have to create the measure inside the bridge table. In Power BI, measures aren’t limited to any one table you can create it in any table you prefer.
After you set up the relationships between the bridge table and both note tables, then you can create the measure to combine the notes based on the shared ID.
as suggested Kedar_Pande the above use RELATED function in your measure to combine the notes based on the shared ID.
Hope this helps !!
