Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |