The best solution would be to get the data sourced into one xlsx sheet - and therefore one datasource. This way the 'system' filter will do the job for you.
If agregating the data manually is not an option, talk to your database extractors about performing a 'union' query to extract from multiple tables, but into one extract. You could even try doing multiple 'data connection' s from within excel - all placed in a singlesheet. I've done this succesfully before - but you have to be careful about the 'override' settings keeping the data intact when refreshed.
If that is a no go for you, a poor mans workaround alternative may be to rename your 'value' in each datasource to indicate where it came from - and then show the 'measure names' filter as a quick filter. It doesn't work like a normal filter - as once a user deselects a measure name - there is no way of getting it back! check out test2.twbx for example
Hope this is helpful
test2.twbx.zip 18.2 KB
thanks for that answer, appreciate your help.
I tried what you said with Excel and worked as expected.
Is it possible at all though to do it using data sources pointing to different databases?
The tables are exactly the same but on different systems using the same DB software. In the future I might have the chance to merge the data together on one system for easier reporting but right now Im stuck with 4 workbooks (with 5 sheets) doing the exact same thing but sourcing from different systems.