Slow changing Dimension with Date Range Filter

[LAndris]

Hello,

I am attempting to create a report in Power Bi that will use a date range slicer to filter the following two tables 

Table 1: Slow changing Claim Dimension with StartDate EndDate and other Foreign keys

SourceClaimId	StartDate	EndDate	RowNumber
119573	2020-02-24	2020-02-24	1
119573	2020-02-25	2020-03-10	2
119573	2020-03-11	2020-06-24	3
119573	2020-06-25	2020-07-29	4
119573	2020-07-30	2020-08-03	5
119573	2020-08-04	9999-12-31	6
123847	2020-06-29	2020-06-29	7
123847	2020-06-30	2020-07-24	8
123847	2020-07-25	2020-07-27	9
123847	2020-07-28	2020-07-29	10
123847	2020-07-30	2020-08-03	11
123847	2020-08-04	2020-08-04	12
123847	2020-08-05	9999-12-31	13
122925	2020-06-07	2020-07-06	14
122925	2020-07-07	2020-08-06	15
122925	2020-08-07	9999-12-31	16

Table 2: Table holding date facts

SourceClaimId	ReportedDate	ClosedDate
119573	2020-02-24	2020-08-04
122925	2020-06-05	2020-08-05
123847	2020-06-29	2020-08-05

Here are the relationships between the date table and Table 1/ 2.

Using a separate date table I am attempting to use a date range slicer to choose the claims from Table 2 based on their closed date, which in turn will filter Table 1 based on the SourceClaimId. However, I also am trying to have the same date range slicer use the end date of date range slicer filter the Table 1 where the records' startdate and enddate encompass the ending date from the date range slicer. 

For example if the period of the date range slicer was from 8/5/2020 to 8/6/2020

The records from Table 2 would be claims 122925 and 12384, and from Table 1 we would expect to see records number 13 and 15.

Any help is much appreciated 

Thanks, 

Louis 

[Icey · Accepted solution]

Hi @LAndris ,

I try to reproduce your scenario, please check if this is what you want:

Measure =
VAR MaxDate_ =
    MAX ( Dates[Date] )
RETURN
    IF (
        MAX ( 'Table 1'[StartDate] ) <= MaxDate_
            && MAX ( 'Table 1'[EndDate] ) >= MaxDate_,
        1
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[lbendlin]

Disconnect your dates table, and then for each of your fact tables add a measure that calculates visibility of each filter context (ie row in visual plus total) based on the min/max values of the date slicer.

Note: Do NOT use CALENDARAUTO(), especially not with dates like "9999-12-31" - That generates an enormous amount of dead weight.  Use your own external dates table, just covering the important date range.