Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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,
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,
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |