Filter one table based on date range in another table

[STEVE_WT]

Hi. 

I've hit a brick wall with this and desperately need some help.

I have an events table with a date range for each event and customers within that event. I also have a transactions table with product and sales info and invoice date. These tables are linked by the customer account field. I have a slicer for the first table by 'event'. See example below. I want to select an 'event' on the slicer and whatever the date range for that event is, I want the transaction table to only show transactions between that date. I'll then have summary tables by product and by customer etc. 

Any help would be greatly appreciated

Many thanks

Steve

[DataNinja777 · Accepted solution]

Hi @STEVE_WT ,

To achieve the desired functionality, you can create a solution using a disconnected calendar table alongside relationships between the tables. Here's how it works:

A disconnected calendar table is a standalone table that contains a complete range of dates and isn't directly related to any other tables in your data model. You would use this calendar table to help define the filtering logic for the transactions table based on the date range of the selected event.

The Events table includes a date range for each event. By selecting an event in the slicer, you can dynamically extract its start and end dates using DAX measures. These extracted dates can then define the date range filter for the transactions table. To establish the link between the two tables, the extracted date range from the Events table will be used to filter the transaction dates in the Transactions table via the calendar table.

The calendar table becomes the bridge that translates the event-specific date range into a format applicable to the Transactions table. While the calendar table is disconnected, it allows you to compare, analyze, and apply filters without creating a direct relationship between the Events and Transactions tables. This setup ensures flexibility and avoids complications arising from bidirectional relationships or unintended cross-filtering.

When this setup is in place, selecting an event in the slicer will trigger measures that compute the relevant date range. These dates will then filter the calendar table, which in turn will filter the Transactions table. You can then build summary visuals or tables, such as product or customer summaries, knowing that only transactions within the selected event's date range will be considered. This method provides a robust and dynamic way to manage your data filtering needs while maintaining the independence of your core tables.

This solution is based on the assumption that the Events table is disconnected from the Calendar table, while the Transactions table is connected to the Calendar table. This separation is intentional and crucial for the solution. Let me clarify this further:

1. Events Table and Calendar Table:

   • The Events table is not directly connected to the Calendar table. Instead, the date range for a selected event (start and end dates) is used to filter the Calendar table dynamically using DAX measures.
   • This ensures the Events table does not create unwanted filter propagation in your data model.

2. Transactions Table and Calendar Table:

   • The Transactions table is directly related to the Calendar table through the invoice date. This connection ensures that any filtering applied to the Calendar table affects the Transactions table.

If you need any further clarifications, please let me know.  

Best regards,

[STEVE_WT]

Perfect! Thanks for the quick response. Much appreciated.

[Anonymous · Accepted solution]

Hi @STEVE_WT ,

As DataNinja777  said, you can create a date table and then model the relationship: when you filter events the TRANSACTIONS table is only affected by the date slicer of the date table and not by the start and end dates of the events.

DateTable = CALENDAR(DATE(2024,1,1),DATE(2024,12,31)) 

Then the result is as follows.

If I've misinterpreted you, please point out your intended outcome in a follow-up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[STEVE_WT]

Thanks a lot for the reply. A great solution! Works perfectly.

[DataNinja777 · Accepted solution]

Hi @STEVE_WT ,

To achieve the desired functionality, you can create a solution using a disconnected calendar table alongside relationships between the tables. Here's how it works:

A disconnected calendar table is a standalone table that contains a complete range of dates and isn't directly related to any other tables in your data model. You would use this calendar table to help define the filtering logic for the transactions table based on the date range of the selected event.

The Events table includes a date range for each event. By selecting an event in the slicer, you can dynamically extract its start and end dates using DAX measures. These extracted dates can then define the date range filter for the transactions table. To establish the link between the two tables, the extracted date range from the Events table will be used to filter the transaction dates in the Transactions table via the calendar table.

The calendar table becomes the bridge that translates the event-specific date range into a format applicable to the Transactions table. While the calendar table is disconnected, it allows you to compare, analyze, and apply filters without creating a direct relationship between the Events and Transactions tables. This setup ensures flexibility and avoids complications arising from bidirectional relationships or unintended cross-filtering.

When this setup is in place, selecting an event in the slicer will trigger measures that compute the relevant date range. These dates will then filter the calendar table, which in turn will filter the Transactions table. You can then build summary visuals or tables, such as product or customer summaries, knowing that only transactions within the selected event's date range will be considered. This method provides a robust and dynamic way to manage your data filtering needs while maintaining the independence of your core tables.

This solution is based on the assumption that the Events table is disconnected from the Calendar table, while the Transactions table is connected to the Calendar table. This separation is intentional and crucial for the solution. Let me clarify this further:

1. Events Table and Calendar Table:

   • The Events table is not directly connected to the Calendar table. Instead, the date range for a selected event (start and end dates) is used to filter the Calendar table dynamically using DAX measures.
   • This ensures the Events table does not create unwanted filter propagation in your data model.

2. Transactions Table and Calendar Table:

   • The Transactions table is directly related to the Calendar table through the invoice date. This connection ensures that any filtering applied to the Calendar table affects the Transactions table.

If you need any further clarifications, please let me know.  

Best regards,

[STEVE_WT]

Hi

One more question. The solution above now filters the transactions table to the required dates of the event name chose but there are set customers in the events table for each event. How do I now filter the transactions table to just those part of the event in the drop down slicer? At the minute it is pulling in all the customers within the date range.

Thanks

Steve