Show records where date falls between two other dates

[sosukeinu]

Hello,
I'm new to PowerBI and DAX, but I have experience using Python and R to work with data. I'm trying to find the best way to deal with this particular problem.

What I'd Like to do:
I have data that contains a number of education records like below:
ID       schyr       sem     start               end

-----------------------------------------------------
12       200203   1        01/01/2002   06/30/2002

12       200203   2        11/01/2002   06/30/2003

13       200203   1        01/01/2002   06/30/2002

13       200203   2        11/01/2002   06/30/2003

.....

I would like to be able to select an arbitrary date like 03/10/2002 and return all records where that date falls between start and end, and the ID is distinct. In the above example, these rows would be returned:
12       200203   1        01/01/2002   06/30/2002
13       200203   1        01/01/2002   06/30/2002

The Way I'm Currently Accomplishing This:

Currently I'm getting my records to a form where they are unique for ID, schyr, and sem. Then, I'm creating a vector of every date between start and end, and duplicating each row x times for the number of days, and appending these dates to the records. The issue with this approach is obvious. It must be done in R before bringing it into PowerBI,...and it takes a records set that has around 64,000 records (where each record means something obvious) and making it a record set with almost 1 million rows.

Any help or thoughts about better ways to do this would be greatly appreciated.
Thank you.

[v-sihou-msft · Accepted solution]

@sosukeinu

In this scenario, you can generate a calendar table for populating slicer.

Calendar = CALENDAR("2002/1/1","2003/12/31")

Create a measure to get the selected date.

Selected Date = MAX('Calendar'[Date])

Then create a measure to compare the start date and end date of term with selected date.

Within Term = IF([Selected Date]>MAX(education[start]) && [Selected Date]<MAX(education[end]),1,0)

Now you can add [Within Term] measure into visual level filter.

Regards,