cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LAndris
Helper I
Helper I

Slow changing Dimension with Date Range Filter

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

SourceClaimIdStartDateEndDateRowNumber
1195732020-02-24 2020-02-24 1
1195732020-02-25 2020-03-10 2
1195732020-03-11 2020-06-24 3
1195732020-06-25 2020-07-29 4
1195732020-07-30 2020-08-03 5
1195732020-08-04 9999-12-31 6
1238472020-06-29 2020-06-29 7
1238472020-06-30 2020-07-24 8
1238472020-07-25 2020-07-27 9
1238472020-07-28 2020-07-29 

10

1238472020-07-30 2020-08-0311
1238472020-08-042020-08-04 12
1238472020-08-059999-12-31 13
1229252020-06-072020-07-06 14
1229252020-07-072020-08-0615
1229252020-08-079999-12-3116

 

Table 2: Table holding date facts

 

SourceClaimIdReportedDateClosedDate
1195732020-02-24 2020-08-04 
1229252020-06-05 2020-08-05 
1238472020-06-29 2020-08-05 

 

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

 

PBI Help.png

 

 

 

 

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 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @LAndris ,

 

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

re.PNGrow.PNG

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.

View solution in original post

2 REPLIES 2
Icey
Community Support
Community Support

Hi @LAndris ,

 

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

re.PNGrow.PNG

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.

View solution in original post

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors