Forum Discussion
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...
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])
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. 😞