I’m not sure if a combined query works with personal data files, but if it does, that’s the way to go. Use a minus combined query, with the DP that has the values first and minus the DP that is missing them.
I just looked back at your message and you’ll probably have to run two combined minus queries to get the values that are in DP1 and not DP2 and then another minus query to get the values that are in DP2 and not DP1.
If you use tables instead of EXCEL sheets, I suppose your SQL query should be something like this
SELECT TABLE1.COLUMN
FROM TABLE1
WHERE TABLE1.COLUMN NOT IN ( SELECT TABLE2.COLUMN FROM TABLE2)
UNION
SELECT TABLE2.COLUMN
FROM TABLE2
WHERE TABLE2.COLUMN NOT IN ( SELECT TABLE1.COLUMN FROM TABLE1)
From the First you will get 5 and from the Second you will get 6.
With all due respect, I think we’re off track here. The question was regarding two linked spreadsheets as data providers. I don’t see how SQL solutions apply.
Take a look at the attached example. There are two data providers whose contents match your sample data. The key is applying a filter to a non-linked object from each data provider.
One report block displays all data (not filtered).
One report block displays only common dimensions … filter = Not IsNull(<M(DP1)>) And Not IsNull(<M(DP2)>)
One report block displays only not common dimensions … filter = IsNull(<M(DP1)>) Or IsNull(<M(DP2)>) Multi DP filtering.rep (45.0 KB)