Forum Discussion
Helper ISlow 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
| 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
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.
2 Replies
- Icey
Community Support
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
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.
