Relationships between two tables not filtering properly

[scruffychain]

I have two tables:

1. The first query is where I get all my data for the reports.

2. The second query was created in order to define the targets for scrap for every material with a specific operation (OP10,Op20...etc.)

I am trying to report the actual scrap against the target.

The cells highlighted in green are the actual scrap values for 4 months (Jan - April 2023).

The cells in between with 1.25% should be the scrap target for evey operation for the specific material number.

The chart below should be what I must be seeing for every operation for the target and not 1.25% all around.

My relationship is setup as below. Material and operation linked.

How can I fix my report such that I see the correct target operation wise for the part number?

Any inputs would be highly appreciated!! 

[scruffychain]

@TheLoonies That did not work. Getting rid of the material relationship made the other table with the targets also throw out incorrect values. 

There are many additional tables in this model however its a live data set and it can't be viewed. 

The operation numbers are not unique. There can be many similar material number and operation number rows in the same table.

[TheLoonies]

Sorry, I don't think I edited my previous reponse very well.

One solution would be to create a new column in both tables which combines the information from material and operation number into a unique code. Then relate the tables with a 1:1 relationship.

For example I'm using index as the name for the new column:

Alternatively you could add a calculated column to the data table.

In my example that would be:

Target = LOOKUPVALUE(Targets[Target], Targets[Material], Data[Material], Targets[Operation Number], Data[Operation Number])

or if you need date info aswell

Target = LOOKUPVALUE(Targets[Target], Targets[Material], Data[Material], Targets[Operation Number], Data[Operation Number], Targets[Date], Data[Date])

[scruffychain]

Unfortunately, except for my Scrap Target table all my other tables use DirectQuery with data from our MES system and I can't access the dataset. 😞

[TheLoonies]

I think the problem is that you have an inactive relationship, presumably the one connecting operation number.

try removing the relationship between material and making operation number active, that would average over the material instead, which would be fine if each operation number is unique.

Otherwise if you create a new column in both tables along the lines of Material&OperationNumber, then you could use those columns to define the relationship. I don't find that solution very elegant, but I don't know of another.

If the operation numbers are not unique then try adding another table  to filter both the MES data and scrap target by