Shared value from two different columns put into one

[docs]

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?  

[Kedar_Pande · Accepted solution]

@docs 

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

[docs]

Hi, do I create the measure in the bridge table and which function should  I use to create the measure?

[v-aatheeque]

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

[v-aatheeque]

Hi @docs 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[docs]

Yes they did. Thank you for your help!