Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Relationships between two tables not filtering properly

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 operati...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Anonymous 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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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])
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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. 😞