12 Replies Latest reply on Oct 2, 2015 8:27 AM by mindy.hoftender.0

    Can I create a calculated dimension based on a parameter when my datasource is a cube?

    Maureen Sheehan

      I am changing an existing workbook to source from an SSAS cube instead of a Tableau extract.  I have a parameter, let's call it "Rows Option" and it is used to define a calculated dimension, let's call it "Selected Field" using a case statement like

      CASE [Rows Option]

      WHEN "Field 1" then [Field1]

      WHEN "Field 2" then [Field2]

      ELSE "ERROR" END

      I can then make "Selected Field" my rows and let the user in Tableau Reader pick which field they want the rows to be via the parameter drop down.

       

      When my datasource is a cube, it looks like I can only use measures for calculated fields. I also see that I can use MDX to define a calculated member of an existing dimension, but not sure how that will help me.  I also thought maybe I could do something like this and just swap out the report:  http://kb.tableausoftware.com/articles/knowledgebase/creating-sheet-selector-for-dashboard  but, it still requires a calculated field that is not a measure.

       

      Is there any way to do something similar to the functionality above?

        • 1. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
          Julia Nguyen

          Hi,

           

          I am facing the same issue and I am looking for a solution with calculated members. If you find anything, please can you share it here?

          • 2. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
            elaine.lightfoot

            Same problem. Connecting to an SSAS Tabular Model. Dimension attributes are not available to Calculated Field.

            • 3. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
              vishwanath Pendyala

              Hi Elaine

               

              Are you trying to get the mesaure value for a dimension or Trying to use parameter  ?

               

              Thanks

              Karthik

              • 4. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                elaine.lightfoot

                I am trying to create a calculated field that will be a dimension attribute. I have a Date dimension with an attribute called Date. Its format is MM/DD/YYYY which this article indicates Tableau will recognize as a date:

                http://kb.tableausoftware.com/articles/knowledgebase/fixing-date-fields

                However, it still interprets the date as a string. That is why I want to create a calculated field where I either replicate the formatting or change to one of the other formats Tableau indicates will be recognized.

                In the Calculated Field window, I enter the formula that references the Date field and I get an error,

                "Reference to undefined field [Date]."

                In the Fields list, I only see measures, no dimension attributes. However, if I point to a relational data source, I see dimensions and measures.

                • 6. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                  elaine.lightfoot

                  Thanks Karthik. Unfortunately, these instructions are for SSAS Multidimensional and not Tabular.

                  • 7. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                    Michael Collier

                    If I understand your question correctly, you want to create a parameter driven dimension, and you are working off a cube as a data source?

                     

                    Sorry if this is not your question, but here's what I did to solve this challenge when I ran into it:

                     

                    If you want to show "Field 1" which is a dimension, and have the option of switching it out to be "Field 2" with your parameter, and report the values associated with each, you need to use MDX language. 

                     

                    You need to create an intersection of the dimension name with the measure value.  As an example:

                     

                    "Right Click" the dimension and choose "Calculated Members".  The window will open, and you will need to click the "New" button.

                     

                    Next, you need to choose your hierarchy; drop down the list and use the measure you want.

                     

                    The field would look something like this: ([Dimension].[Field 1], [Amount].[USD]) and name it "Field 1".

                     

                    The above MDX will drop Field 1 down into the measures pane.  This gives you the measure value of Field 1 when you drop it on the rows or columns shelf.  Now, create a measure using MDX for "Field 2". 

                     

                    Now, create a calculated field that works off a parameter: if [parameter goofball]="Field 1" then [Field 1] elseif [parameter goofball]="Field 2" then  [Field 2] end

                     

                    Last, create a parameter to operate the calculated field.  Now you can change the row value for field 1 or 2 by the parameter.  This is the concept in it's simplest form.  From here, you can change the accounts dimension, create multiple intersections in the same MDX, combine it with a utilities dimension, and more I haven't learned yet.

                    • 8. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                      Adam Green

                      Im trying to do the same as Maureen Sheehan, but unable to get what Michael Collier suggest to work.

                       

                      // 

                      I am changing an existing workbook to source from an SSAS cube instead of a Tableau extract.  I have a parameter, let's call it "Rows Option" and it is used to define a calculated dimension, let's call it "Selected Field" using a case statement like

                      CASE [Rows Option]

                      WHEN "Field 1" then [Field1]

                      WHEN "Field 2" then [Field2]

                      ELSE "ERROR" END

                      I can then make "Selected Field" my rows and let the user in Tableau Reader pick which field they want the rows to be via the parameter drop down.

                      //

                       

                      i am in the calculated Members window and have written the MDX string as i think should work but its not (so clearly have something incorrect).

                      category parameter.png

                      Any help would be gratefully received

                       

                      Adam

                      • 9. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                        Michael Collier

                        Adam -

                        My approach assumes the rows are static and the Parameter changes the values shown in the Rows.  It uses Calculated Members from a Dimension, and the values change according to the Parameter (one Measure with all the Calculated Members via a calculated field using multiple "if" statements referencing the Parameter).  The same approach can likely be used for dynamic rows, but I don't know how you will change the row name... only the value would change in my example.

                         

                        AND...

                        I did create dynamic columns/rows using an "if" statement.  This approach will work if you have all your rows underneath "Application Applicant", and in your image it looks like you do. Put your Dimension on Rows. The parameter drives which rows will show based on the parameter.

                        Here is the statement:

                         

                        Case [Scenario Comparison] (this is the parameter)

                        When "Forecast vs Budget" then (these are the rows I want to show)

                           size()-index()+1=1

                        or size()-index()+1=4

                        When "v2 Forecast vs Budget" then

                           size()-index()+1=1

                        or size()-index()+1=3

                        When "Actuals vs Budget" then

                           size()-index()+1=1

                        or size()-index()+1=2

                        end

                         

                        I found when I set this up, it's best to put the calculated field on the rows with no filters so I can see what comes up "True" and what comes up "False"  This helps me adjust the size/index total number to get the right rows to show up.

                         

                        When I got the numbers right for the size/index calcs, I put the calculated field in the Filters pane and select "True", now when you flip the parameter, the different rows will show up.

                         

                        Parameter that Adjusts Rows.PNG

                        • 10. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                          Adam Green

                          Hi Michael,

                           

                          Sorry i think i may need to go back a few steps. previously (when connected to an excel sheet) i was able to write a very simple calculation (create calculated field):

                           

                          eg:

                           

                          calculated field would be called 'Category':

                           

                          CASE [Category_Parameter]

                          WHEN "Programme type" THEN [Programme type]

                          WHEN "Gender" THEN [Gender]

                          WHEN "Fee status" THEN [Fee status]

                          WHEN "Nat grp" THEN [Nat grp]

                          WHEN "Dom grp" THEN [Dom grp]

                          WHEN "Age Group" THEN [Age Group]

                          END

                           

                          create a parameter in this case 'Category_Parameter' and then i would have a nice report that would have static rows, but have the 'Category' along as a column. and i would have the Category_Parameter as a quick filter to be able to toggle between Programme type, gender, fee status etc

                           

                          see below for example.

                           

                           

                          however recreating this when connecting to a cube for a slightly different report (using Applicant category and Widening participation for example) is proving troublesome.

                           

                          firstly in my cube - my row fields are held in a different parent dimension 'Application Course', my profile info (that i want to filter) is located under parent directory 'Application Applicant' - does this make a difference in the calculation needed?

                           

                          i have built a Parameter - called category parameter as before, but can seem to be able to create the calculated field as before. i think from what has been said i must create a calculated member (but this doesnt appear to create a 'field' as it were to put into the column, to be used with the parameter filter to toggle between profile types. it seems to just add an additional field/element to the dimension: [Application Applicant. [Applicant Category] i guess because i have put it under the hierarchy?

                           

                          Would you mind starting from scratch (with this in mind) to help me build this example as a working principle whereby i could have a set of reports with potentially static rows but toggling columns based on a parameter filter (which may link to dimensions in the cube with different parent dims).

                           

                          apologies if this is unclear, let me know if you think it is better for me to contact tableau direct?

                           

                          Adam

                          .

                          • 11. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                            emmanuel.owusu

                            Hi Elaine, I think I am experiencing this same problem in tableau. Did you ever find a way around it? Maybe you can assist me as well

                            • 12. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
                              mindy.hoftender.0

                              The above solution did not work for me either.

                               

                              I'm hoping that more people start using Cube data in Tableau. There seem to be a lot of unanswered questions.