4 Replies Latest reply on Dec 30, 2015 10:15 AM by Hidahis Mesa

    Is it possible to create a filter action between two multi-dimensional data sources?

    Jennifer Nash

      I'm looking to create a filter action between two Microsoft cube data sources.  One data source is actually a duplicate of the other which I created because I am using global filters and I don't want the global filter to apply to one (or more worksheets) in my workbook.  It seems relatively straightforward when using relational data sources but I'm not having much luck with Microsoft cube.  Any assistance is much appreciated!

       

      Jen

        • 1. Re: Is it possible to create a filter action between two multi-dimensional data sources?
          Russell Christopher

          Hey Jennifer -

           

          I think this one may be one for Tech Support. Your question made me curious, and I tried the same thing myself just now. Can't do it. I can create the Action filter, but then it appears to be ignored.

           

          One thing I spotted was interesting to me: One can choose to use "Selected Fields" in an action filter, and then literally choose the fields and data sources which should participate in the Action Filter. When I tried this, I saw that I was unable to choose the second cube.

           

          Here, we see the expected behavior (relational sources)...I'm matching a field from one relational source against a field in a different source:

           

          ScreenHunter_01 Aug. 22 06.14.gif

           

          When I try the same thing with cubes, the "Target" data source is disabled:

           

          ScreenHunter_02 Aug. 22 06.17.gif

           

          I know Tableau can't do Data Blending between cubes, so I'd guess we're somehow limited here too? Just not positive. I think Support will know for sure, though.

           

          Good luck! Would love to know the outcome.

          • 2. Re: Is it possible to create a filter action between two multi-dimensional data sources?
            Jennifer Nash

            Thanks for the response.  I'll pass this along to Tech Support and let you know what I hear back.

            • 3. Re: Is it possible to create a filter action between two multi-dimensional data sources?
              Matt Morgenroth

              Hello All, This is Matt from Support. I just got this case.

              Here is the key line from our Online Help that spells out the requirement:

              "When using a multidimensional data source, the destination sheet must use the same data source as the source sheet. Moreover, the source field names must match the destination field names."

               

              So with Multidimensional sources, the source must be the same.  This means that even a duplicate of the same source will not work.  This is likely due to the need to support drilling into dimensions and the complexity of supporting addressing the data across data connections via filter actions.  MDX can be tough in that regard.

              • 4. Re: Is it possible to create a filter action between two multi-dimensional data sources?
                Hidahis Mesa

                To filter two cube database sources you can mostly following this tutorial with a few modifications.

                http://kb.tableau.com/articles/knowledgebase/filter-data-sources


                For step 4, because you cannot reference a cube dimension in a calculated field, you need to create a calculated member for the field you want to filter. Lets say you name your calculated member State Calculated Member. Make sure the result type is set to String. For the formula use appropriate MDX (which should be something like [State].Currentmember.Name).

                 

                The State Filter calculated field formula in step 4 will now equal [Select State]=[State Calculated Member].

                 

                You then need to place the original cube dimension State somewhere in the view. It can either go in Detail on the Marks Card or in Rows/Columns.

                 

                In the tutorial you simply drag the State Filter measure to the Filter Card and select True. For some reason I got a "Invalid field formula due to limitations in the data source" error when I tried this.

                 

                To work around this error I created another calculated field called State Filter Numeric using the following formula:

                 

                If [State Filter] = TRUE

                THEN 1

                END

                 

                You can then place the State Filter Numeric measure into the Filter Card and select a range from 1 to 1.