1 Reply Latest reply on Apr 12, 2018 2:45 AM by Neil Holder

    Filtering All Using Related Data Not Working

    Neil Holder

      Hi All,


      I would appreciate an experts help with yet another frustrating issue.  I have 3 data sources:


      1. Data Source 1 - Contains a bunch of measures please a Key.
      2. Data Source 2 - Contains a bunch of measures please a Key.
      3. Full List Of Keys - Contains all Keys from 1 & 2, with additional dimensions for filtering


      I'm specifically trying to use the filtering option "All Using Related Data".  I'm aware that I could join all the data prior to this, however, there will be instances where I just want the data from 1 or 2, and I'm cautious to join it all together since I'd potentially be pulling a lot of data and affecting performance.  I'm also aware that I could use a parameter, but this seems like an older work around from a time this newer option didn't exist and presumably wouldn't allow me to pull multiple filters from 3, where one filter reduces the options of another (i.e. cascading filters).


      Unfortunately, due to confidentially I can't share the workbook, but I've tried stripping it right back and created some alias' for screenshots below (note I've blanked out the dimension names, just in case they included stuff I should make public...they shouldn't, but better safe than sorry).



      For my stripped back test, I've created the above sheet, set the full list as primary, added a couple of filters with the option "All Using Related Data" and ensured that Data Source one is related via the unique Key.


      I've then created new sheet, added a random count to it from data source 1, making it primary.  This doesn't pull in the the filters I added to the previous sheet, so I ensure the Key is "selected".  As you can see the filters still aren't pulled through.


      Based on what I've read here and elsewhere around the web, this seems like it should work, but simply doesn't.


      I feel like I'm doing something fundamentally wrong, and would very much appreciate someones help/guidance.


      Many thanks, in advance.

        • 1. Re: Filtering All Using Related Data Not Working
          Neil Holder

          So after further investigation it seems that this filter option "all related data sources" only works with fields that appear in both data sources.


          This helps better explain my situation I think.


          If I have two data sources that relate on a single ID field how can I use fields in one data source to filter the other across multiple sheets?


          My reasoning for not using custom SQL is simply to avoid performance hits on having to join a few more tables.