Connect two different column from two sources

[jps_HHH]

Hi all,

I've a powerBI with two sources (or two lists as database). 

I'd like to connect the two lists throught product name and batch number.  

List 1

In that list there is a row per each pair "product -> batch number".  But the product can be repeated in several rows (because the batch number change) and the batch number can be repeated because two different product can be the same batch number. 

Product	Batch number	Date 1	Date 2
A	100	2014	2015
A	101	..	..
B	200	..	..
A	105	..	...
C	201	..	..
B	201	..	..
C	202	..	..

List 2

In that list is showed the failures detected per pair "product -> Batch".  More than 1 product/batch failure could occur.

Product	Batch number	Failure description
A	100	bla bla
A	100	..bla bla	..
B	200	bla bla	..
B	200	bla bla	...
B	200	bla bla	..
C	202	bla bla	..
C	202	bla bla	..

[danextian]

Hi @jps_HHH 

This should be ideally pushed upstream. Add a column in your database called PrductBatch which is the two columns concatenated for both tables/lists.  Create another table/view that unions the ProductBatch columns returning only the distinct values.  Load the two tables with the new column  and the union table. Your model will look like this. Use the column from the bridge table in your viz and the aggregated data from the other two tables.

I used power query to create the new columns in the sample pbix. There is also one for DAX (least priority).

Notoe: Relationship is one-to-many and single direction.

[jps_HHH]

Is it not possible to create a new column in each table with "productbatch" (the merger of product name with bathc number) and connect the two tables by "productbatch" ? 

[jps_HHH]

I expected that it would be more simple to solve.  I will try

[danextian · Accepted solution]

That's another way to do it but you might run into some problems in the future when you add more tables.  Ideally, what you want to do is to connect a dimension table to a fact table. Sometiems you connect  a fact table to another fact table but that is usually more on the case of, for example, a sales order table to sales order lines/items table.