I've been using Tableau for a couple months now and most of my projects only involved relational databases. I just recently got my first one that involves 1 cube and 1 relational database. For the cube, the date is in a hierarchy and is formatted as a string.
Cube Ex (Connected to SSAS):
[Opp Closing Date].[Date].[Date] 'formatted like Jan 1, 2018
[Opp Closing Date].[Calendar Year Hierarchy].[Month Name] 'formatted like January
[Opp Closing Date].[Calendar Year Hierarchy].[Quarter] 'formatted like Q1
[Opp Closing Date].[Calendar Year Hierarchy].[Year] 'formatted like 2018
The Measure will be Expected Sales Volume
Relational Database Ex:
The relational database uses the Created dimension that's formatted as date (Formatted at January 1, 2018). This dimension is for when a request gets assigned to an analyst and will be filtered with a dimension called "Status". The "Status" dimension will be set to "Closed" which means the analyst finished the request.
Goal: My goal is to create a dashboard and use a parameter or filter that utilizes both the "Opp Closing Date" with the "Created" dimensions to show the total request closed by the analyst and how much the "Expected Sales Volume" will be for that given time. I try to convert the string "Opp Closing Date" to date format but I get many null values. I'm extremely new when it comes to using MDX and Calculated Members. Can someone help me with this situation? I tried to be as detailed as possible and I hope this makes sense. I was hoping that if both dimensions were formatted as date, I could blend the data by the date fields.
Maybe create a parameter that shows the quarters or current/previous week or month.
Cube data sources can only be used as the primary data source for blending data in Tableau. They cannot be used as secondary data sources.
For cube data sources, you cannot use parameter values to filter dimensions in an MDX calculation.
Refer cube limitations