Forum Discussion

LAndris's avatar
LAndris
Icon for Helper I rankHelper I
6 years ago
Solved

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