Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have a table with a large number of records which contains daily snapshots of each item (let's call this table "Historical"). I also have another table which contains two date fields which has a "Start Date" and an "End Date" column for every schedule (let's call this table "Range").
Sample data for Range table (the Start Date and End Date usually overlaps but there could be instances when they don't):
Schedule | Start Date | End Date | ||
A | 12/27/2023 | 1/10/2024 | ||
B | 1/10/2024 | 1/24/2024 | ||
C | 1/24/2024 | 2/7/2024 | ||
D | 2/7/2024 | 2/21/2024 |
The "Historical" table contains a date column (let's just call it "Date") that indicates when the snapshot was taken. I would like to filter out the records so that it only loads snapshots that were taken on the "Start Date" and "End Date" of the "Range" table and exclude all other daily snapshots so that I can reduce the data refresh time and decrease the table size.
It is possible to do this during Transform?
Hi @ErPat
For your question, here is the method I provided:
Here's some dummy data
“Range”
Historical = CALENDAR("10/1/2023", "3/20/2024")
Create a measure. Search for dates in the "Range" range.
range_date =
var _startDate = MIN('Range'[Start Date])
var _endDate = MAX('Range'[End Date])
RETURN
CALCULATE(
SELECTEDVALUE('Historical'[Date]),
FILTER(
'Historical',
'Historical'[Date] >= _startDate
&&
'Historical'[Date] <= _endDate
)
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-nuoc-msft,
Thanks for your response. Appreciate it a lot. I am actually looking for a way to filter out the data during the load process so that all the records from Historical that does not match the Range table will not be loaded during data refresh.
Is there a way to do this?
Hi @ErPat
If you want to filter the data during the load data phase, you can go to the Power Query editor.
This allows you to filter the data before it enters the model.
You can set a date range in the Power Query editor to include only data from a specific time period, or exclude unwanted records based on certain criteria.
Select the columns you wish to filter and click on the drop down option to find Custom Filtering.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks again @v-nuoc-msft but the Power Query filter just lets me input arbitrary dates and not based the value on another table column. I am not sure if my scenario is even possible. Thanks again!
User | Count |
---|---|
92 | |
86 | |
67 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |