I'm looking at Product dimension table.
It has got products table obtained by performing a SQL UNION between Product tables in 2 databases. The columns are: Dbname,productid,productname,productcategory
The same product name exists in both databases but with different ID. The unique key of this table is concat(dbname,productid).
Similarly, the sales table is created using the UNION approach and is related to product table on dbname,productid.
I have brought the product and sales tables into the power bi data model, and created a new column in both tables called key (concat dbname,productkey). And then created a relation on the dbname.
This is similar to the situation where multiple customers have the same name. The default situation is for PowerBI to aggregate common values together. e.g. the following table:
when placed in a table visual looks like this.
Even though there are two John Smiths who are separate people, they have been aggregated. To avoid this, set a key on the table and a row value like this:
When you add the same columns to a table, they will now be separate.