Advice on correct method of establishing relationship using a common denominator between two tables

[RichardJ]

Hi,

I've been struggling to figure out the best way to establish the realtionship(s) necessary to compare customer demand to production availability.  (Link to PBIX)

I have the following (incorrect) relationships established at present

Current Relationship

The list of 'Unique Part Numbers' were taken from the Customer Demand Table and grouped so that only unique Supplier Material Numbers exist in this table. I had hoped this would act as a common denominator between both the Customer Demand and Production Schedule tables.

When selecting a unique Supplier Material Number, the Customer Demand and Production Schedule Supplier Material Numbers do not filter accurately.

Click on a Part Number to view both tables

The Total Production Slots and Empty Production Slots are incorrect also.

Current Incorrect Values

What i've been trying to achieve is this behaviour.

(If we use Part_22 as an example the figures shown below are the desired response)Desired Behaviour

Any assistance on how to structure the data and the relationships would be appreciated.

I'd be happy to merge tables or totally change my approach.

The PBIX can be downloaded here : https://www.dropbox.com/s/fkv7uvxsusiurp6/Demand_Production_Question.pbix?dl=0 

Thanks,

Richard

[RichardJ · Accepted solution]

Hi,

So far have managed to correct the data shown in the Production Demand table by changing the direction of this relationship to 'both' ways. (Link to updated PBIX) 

Changed relationship to both ways

The remaining issue is the syntax for the 'Empty Production Slots' column in the  'Unique_Part_Numbers_From_Customer_Demand' table 

Empty Production Slots = 
CALCULATE(
COUNTROWS('Production Schedule'),
   FILTER('Production Schedule','Production Schedule'[Empty Slot Count]=1)
)

// I would like this column to show the empty production slots for the Supplier Material Number.
// I'd hoped that establishing the correct relationship(s) would correct this figure

I must have the incorrect syntax for trying to filter on a related table.

Can anyone advise on the correct syntax?

Thanks,

Richard

Can anyone advise the correct syntax to only select 

[RichardJ · Accepted solution]

Hi,

So far have managed to correct the data shown in the Production Demand table by changing the direction of this relationship to 'both' ways. (Link to updated PBIX) 

Changed relationship to both ways

The remaining issue is the syntax for the 'Empty Production Slots' column in the  'Unique_Part_Numbers_From_Customer_Demand' table 

Empty Production Slots = 
CALCULATE(
COUNTROWS('Production Schedule'),
   FILTER('Production Schedule','Production Schedule'[Empty Slot Count]=1)
)

// I would like this column to show the empty production slots for the Supplier Material Number.
// I'd hoped that establishing the correct relationship(s) would correct this figure

I must have the incorrect syntax for trying to filter on a related table.

Can anyone advise on the correct syntax?

Thanks,

Richard

Can anyone advise the correct syntax to only select 

[RichardJ · Accepted solution]

Writing down this question/summary helped me rethink the solution.

I ended up using a measure to achieve the end result.

# Empty Slots = 
COUNTROWS(FILTER('Production Schedule','Production Schedule'[Build Slot Status]="Empty Slot"))

Updated PBIX