I have 2 tables (lets call A and B) which evaluate seperate measures. They are both joined to a lookup table (lets call C) for 'store' which has some more join information for attributes, etc.., which we use for display level filtering.
What I want to achieve is filtering both ways A and B to only show the IDs (and information from C) which exist in BOTH A AND B.
Is there some smart way I can do this? I tried doing it in my SQL view level, but problem is that comparing different time periods eventuates out of sync 'stores'.
I need to do this to show like-for-like metrics, and ignore the rows which do not exist in both A AND B.
I need this to work with many different interactive visual level filters from C, so it needs to be dynamic, and re-evaluate if the rows exist in A AND B each time the filters are changed. (dates, etc).
I hope this makes sense? and I hope there is a way.
Thanks,
Jake
