I have a requirements to create dashboard with a bar chart that has three measures on it.
1. Number of received Surveys
2.Number of valid emails
3.Number of responses
And one filter with Month Year
So far is all good.
But here is a problem these measures are calculated from different tables and these tables are connected.
Every measure has to be calculated based on it own date. Means:
Number of Surveys based on Survey Received Date;
Number of valid emails based on Email Sent Date
Number of responses based on Response received date.
But they chart should be changed according to Month Year selection on the Filter that should use a date that includes all these three dates.
I was thinking to create some sort of Calendar and connect all these tables to that Calendar but in this case I would have circular reference as the tables are already connected.
Any Ideas if this is possible to do?
The three metrics should share a time dimension. Is there a way to write this into the data model? Possible suggestions could be including the date into the key - eg combine survey ID and month, or creating a transaction table.
Otherwise, there could be workarounds involving synthetic dimensions and/or data islands....