Compare dataset with another dataset for multiple Columns/Values SSRS

This is the image reference. Let me explain the scenario.

There are two datasets A and B as you can see, dataset B has actual tests values and dataset A has target values (more like ranges). code compares each test (BLK ...) value to the target test (BLK ...) value, code is shown below

=IIF(Len(Lookup("UR_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target")) <= 0,
(
IIF(Fields!BLK.Value > Lookup("UW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow",
        IIF(Fields!BLK.Value < Lookup("LW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow","Green"))
),

(
IIF(Fields!BLK.Value > Lookup("UR_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Red",
    IIF(Fields!BLK.Value > Lookup("UW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow",
        IIF(Fields!BLK.Value < Lookup("LR_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Red",
            IIF(Fields!BLK.Value < Lookup("LW_Limit",Fields!Limits.Value,Fields!BLK.Value,"Target"),"Yellow","Green"))))
))

Now the problem I face is, that I cant compare each products test (from B) values to that specific products target values in dataset A. What ends up happening is that each test in B is compared by last target product values in dataset A.

Concatenating product and limit fields it is possible as mentioned in comments. The lookup function can use any string you pass to search for a value. Note the following example:

Lookup(Fields!Product.Value & "-" & "UR_Limit",...,"TestTargetDataSet")

It will look for 905200-UR_Limit and return the BLK value for upper limit from the Test Target Values. Of course you have to create a calculated field in the TestTarget Dataset and set it as the concatenation of product and limit fields.

I've recreated your scenario using the tables you provided. First I created the calculated field named PRLimit in the Test Target dataset and set it to this expression:

=Fields!Product.Value & "-" & Fields!Limits.Value

As you mentioned in comments you have multiple columns, you have to use a different expression for each column:

=Switch(
Fields!BLK.Value >
  Lookup(Fields!Product.Value & "-" & "UW_Limit",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Red",
Fields!BLK.Value >
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Yellow",
Fields!BLK.Value =
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Green",
Fields!BLK.Value <
  Lookup(Fields!Product.Value & "-" & "LW_Limit",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Red",
Fields!BLK.Value <
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BLK.Value,"DataSet9"),
  "Yellow"
)

---

=Switch(
Fields!BW.Value >
  Lookup(Fields!Product.Value & "-" & "UW_Limit",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Red",
Fields!BW.Value >
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Yellow",
Fields!BW.Value =
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Green",
Fields!BW.Value <
  Lookup(Fields!Product.Value & "-" & "LW_Limit",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Red",
Fields!BW.Value <
  Lookup(Fields!Product.Value & "-" & "Target",Fields!PRLimit.Value,Fields!BW.Value,"DataSet9"),
  "Yellow"
)

The above expressions are used to get the right color for BLK and BW columns respectively. The logic used is:

• IF BLK value is greater than the UW BLK value: Color in Red
• ELSE IF BLK value is greater than the Target value: Color in Yellow
• ELSE IF BLK value is equal to Target value: Color in Green
• ELSE IF BLK value is less than the LW BLK value: Color in Red
• ELSE IF BLK value is less than the Target BLK value: Color in Yellow

This is the final result with the data provided in your question:

Let me know if this helps.