Filter date column other table based on max selected date in slicer.

[ngdata]

Hi

I wonder if anyone could help me please. I have a data model consisting of a student table, transaction table and a dimdates table.

The students is connected to the transactions via an ID and the DimDates is connected to the transactions table via date. I also have an inactive relationship between Students[StartDate] and the DimDates[Date] table.

Using a DimDates slicer on the page i want to filter out the list of students in my matrix so that the start date <= MAX selected date of this slicer. 

Could someone point me in the right direction please? 

Many thanks for your help. 

[Anonymous · Accepted solution]

Hi, @ngdata 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

getSlicerDate =

VAR _maxDate =

    CALCULATE ( MAX ( DateSlicer[Date] ) )

VAR _getStartDate =

    SELECTEDVALUE ( Student[StartDate] )

RETURN

    IF ( _getStartDate <= _maxDate, 1, 0 )

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Greg_Deckler]

@ngdata Perhaps a Complex Selector like this: The Complex Selector - Microsoft Fabric Community

Selector Measure = 
  VAR __Date = MAX('DimDates'[Date])
  VAR __StartDate = MAX('DimStudents'[StartDate])
  VAR __Result = IF( __StartDate <= __Date, 1, 0 )
RETURN
  __Result