Using a dimension field different from the setAnalysis field

[Anonymous]

Hi all,

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.

{$<[Opened Date]={">$(=Date(Max(CalendarDate)-7))<=$(=Max(CalendarDate))"}>}

Also, I will want to do the same based on [Closed Date]; hence why I can't just rename the [Opened Date] field to CalendarDate.

Any help would be greatly appreciated.

[rohitk1609 · Accepted solution]

Hi Simon,

In my understanding you need to do first some data modeling with respect to dates. Read the attached link blog once carefully then try to implement it and let me know if there is any issue.

Canonical Date

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Rohit Kumar