-
1. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
Julia Nguyen Mar 25, 2014 2:03 AM (in response to Maureen Sheehan)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 Jun 5, 2014 12:03 PM (in response to Maureen Sheehan)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 Jun 5, 2014 12:11 PM (in response to elaine.lightfoot)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 Jun 5, 2014 1:25 PM (in response to vishwanath Pendyala)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 Jun 5, 2014 1:48 PM (in response to vishwanath Pendyala)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 Jun 5, 2014 4:09 PM (in response to Maureen Sheehan)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 Aug 20, 2014 7:12 AM (in response to Michael Collier)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).
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 Aug 20, 2014 9:02 AM (in response to Adam Green)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.
-
10. Re: Can I create a calculated dimension based on a parameter when my datasource is a cube?
Adam Green Aug 22, 2014 7:14 AM (in response to Michael Collier)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 Sep 11, 2015 11:41 AM (in response to elaine.lightfoot)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 Oct 2, 2015 8:27 AM (in response to Michael Collier)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.