How to compare two of the same fields in different data sets

Question

I have these 2 datasets

• Categories bought which shows the categories the customer has bought from
• All Categories which is all the item categories

What i want to do is compare the Categories Bought psd_parentproductcategoryid and compare it with the All Categories psd_parentproductcategoryid to pick up the categories which don't appear in the Categories Bought results

I am using FetchXML and not SQL.

Answer 1

You should be able to use a lookup for this:

=lookup(Fields!psd_parentproductcategoryid.Value,Fields!psd_parentproductcategoryid.Value.Value, Fields!psd_parentproductcategoryid.Value, "AllCategories")

The filter part is what can be tricky. I can't find my example but there is a way. Hopefully this can at least get you started.

**Edit

I have a paramter named Present. The value passed when I want to filter the dataset is 0.

Here is the filter code on the tablix:

=iif(Parameters!Present.Value = 0 , lookup(Fields!psd_parentproductcategoryid.Value,Fields!psd_parentproductcategoryid.Value.Value, Fields!psd_parentproductcategoryid.Value, "AllCategories") is nothing, "FALSE", "TRUE"), "TRUE")

**Edit 2 You can add this to your filter to only return values not present in your other data set.

IIF(=lookup(Fields!psd_parentproductcategoryid.Value,Fields!psd_parentproductcategoryid.Value.Value, Fields!psd_parentproductcategoryid.Value, "AllCategories") is nothing, Fields!psd_parentproductcategoryid.Value, nothing)