Forum Discussion
Filtering Slow Changing Dimension and Facts table with Date Range Slicer
Hello,
I am attempting to use a date range slicer to filter the following two tables
Table 1: Slow Changing Claim Table with Foreign Keys.
Table 2: Date Facts Table
Here is the relationship that was created in PBI.
What I am attempting to do with the date range slicer, but have been unable to figure out how to filter Table 2 based on ClosedDts between my date range slicer's start date and end date. This in turn would have Table 2 filter Table 1 to claims closed within the date range slicer. However, I also intend to use the date range slicer's end date to filter to the correct record in Table 1 where the startdate and enddate encompass the date range slicers end date.
For example:
If the date range slicer chooses dates 8/5/2020 to 8/10/2020 the claims from Table 2 would be claims 119573 and 123847. These Claims would then filter Table 1 to records 7-18, the end date of the date range slicer would then look for records in Table 1 where the start date and end date have the date range slicers end value between the two. Thus the records for Table 1 would be records 18 and 11.
Any help is much appreciated
Thanks,
Louis