Measure Counting Rows Based Off Two Related Tables

[hmt1]

Hi everyone, still kind of new to this. Normally I just have pretty striaght foward requests but this one has me using a secod table to validate I am count correctly. I am struggling with a measure to count locations where the what was sent matches what was requested. I have three tables and look like the following:

1) locations

2) what was shipped

3) what was requested

Table 1 Locations:

Location	Last Shipment
Location ID 1	Shipment ID 1
Location ID 2	Shipment ID 1
Location ID 3	Shipment ID 2

Table 2 Shipment:

Shipment ID	Product
Shipment ID 1	Item 1
Shipment ID 1	Item 2
Shipment ID 2	Item 5
Shipment ID 2	Item 3

Table 3 Assigned:

Location	Product
Location ID 1	Item 1
Location ID 1	Item 2
Location ID 2	Item 1
Location ID 2	Item 2
Location ID 3	Item 4
Location ID 3	Item 3

I am trying to count by product items how many locations got the items they are assigned and how many were correctly shipped. The visual on the dashboard was expected to look something like this:

Product	Locations With Product	Correct Locations With Product
Item 1	2	2
Item 2	2	2
Item 3	1	1
Item 4	0	0
Item 5	1	0

Location 3 got sent shipment 2 with products 3 and 5 but should have gotten 3 and 4. So item 4 is 0 for never being shipped and item 5 is counted as being at a location but show 0 as being correct locations. I am finding I can count locations with a product to get the first and second column but not sure how to get the count of correct locations. 

I have tried filltering where item shipped = item assigned and got errors on relationships of tables.

Found this one but not having any luck with it either.

COUNTROWS (

        SUMMARIZE ( FILTER ( location,

            location[id] IN VALUES ( assigned[location_id] )

            && assigned[item_id] = shipped[item_id]

            )

        , location[id] )

    )

Can I not count locations based on two related table matching?

Edit: Added relationship image

[v-easonf-msft]

Hi, @hmt1 

Sorry, I'm still confused about the results for items 4 and 5 for "Correct Locations With Product", can you explain more specifically?

Best Regards,
Community Support Team _ Eason

[hmt1]

The goal is to show how many locations currently have the product on site in column 2 (count of locations with product) and also show how many of those products ended up at the correct locations in column 3. This is an audit type dashboard.

I can count the locations each product was shipped to and get the right numbers. When I then try to filter it down to correct locations I am stuck. Below the "Product" in the rows is coming from the shipment table. The values is just the number of locations in the related location table that got the shipment.

If a product "Item" has to be at 5 locations but only 4 of them are correct locations we can only get paid for 4 locations. In the image above it you can see "Item 5" is at 1 location but looking at the assignment table it should never have been there so 0 correct locations should be counted in the second column.

So it is known what the location has based on the last shipment via the shipment id and then it needs to be compared to the assignment table. If the product in the shipment last at a location matches what was assigned for that location it should be counted.

Thanks for any help you can give!