Using a dimension field different from the setAnalysis field
I have searched and searched but to no avail at the moment.
I have multiple tables, all incorporating the field CalendarDate apart from one which has 2 columns: [Opened Date] and [Closed Date] neither of which should be renamed as CalendarDate (that would be too easy and inaccurate).
All charts use the dimension CalendarDate. No exceptions. However, when I want to create the chart based on the number of items where [Opened Date] = CalendarDate, it obviously shows nothing on the chart because the chart's dimension is CalendarDate and that table does not contain the CalendarDate field.
How can I have the chart show the data whilst still using the CalendarDate dimension? I am guessing I need a complex SetAnalysis in there. I already have a setAnalysis in my measure to only show the last 7 days of the master calendar I created (which contains the master CalendarDate field) as such and works beautiful so that's not a problem.