I have spent some time fitting our data to the model offered in Henric's excellent Canonical Date post. I am working on an application where my data looks like this:
The Attempts table is a list of tasks with unique keys. When an employee attempts a task, a new record is created to show the attempt. This creates a many-to-one relationship between attempts and unique tasks. The key and date are brought into the DateBridge table and in this instance the status is set to 'Attempted'. The calendar table is then brought in to use as the canonical date for filtering purposes.
The problem I am facing is that when I filter on a date, other dates turn up in the results if a key has a listed attempt on another date. For example, if I have the following data in the Attempts table:
If I have a filter that uses the Calendar table's Date field, and I set the filter for 2017-11-06, I would expect to see three records, but instead I am seeing all four since two share a key.
How can I get the filter to show only records for the date specified, even if there is another record for a key?