Forum Discussion

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

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 o...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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.