topic Re: Filter date column other table based on max selected date in slicer. in DAX Commands and Tips

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

ngdata — Mon, 19 Feb 2024 15:13:15 GMT

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.

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

Greg_Deckler — Mon, 19 Feb 2024 16:06:36 GMT

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

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

Anonymous — Thu, 22 Feb 2024 08:59:28 GMT

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