Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I set everything up with a filter step converting our date key but when I publish it, it doesn't overwrite the default RangeStart and RangeEnd parameters. The data in Power BI service ends up filtered to the default.
This is what I used for the conversion/filter step:
= Table.SelectRows(#"Source", each [REQUESTED_DATE_KEY] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [REQUESTED_DATE_KEY] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))
HI @anita9,
If you mean preview feature incremental refresh which based on query parameters, it not support dynamic overwriting parameter, you need to manually modify them.
Incremental refresh in Power BI Premium
Override effective date
We plan to allow setting the current date for a refresh operation. This will be useful to use with datasets like Adventure Works that don't have data up to the current date, and for testing purposes.
BTW, you can also add filter in query edit and manually modify formula to achieve dynamic filters based on lastdate or local date.
Regards,
Xiaoxin Sheng
I'm probably using the wrong terminology. When I created the RangeStart and RangeEnd parameters, I used 6/1/2018 for RangeStart and 6/5/2018 for RangeEnd. All of the documentation I've been able to find seems to suggest that it doesn't matter what you put in as the default because once you publish and refresh in Power BI Service, it will refer to the incremental refresh policy to overwrite the default for those parameters and will pull in all of the data that you select to keep (in my case, 4 years) and on subsequent refreshes will refresh what you select to refresh (in my case 5 days).
When I publish and refresh, only those five days from my default parameter filter are there. In the dataset management area, under "Parameters" it shows my default dates and allows me to change them. In other posts, it seems like this should be disabled.
It is giving me neither the 4-year history nor the latest 5 days update. Are you saying that I need to manually adjust the parameters each day to the last 5 days? It would give me the latest 5 days, but I still wouldn't have the 4-yr history, right?
After you set up the parameters and did your "Apply and Close". Did you right click on the tables in the Field Picker and select "Incremental Refresh"? Also just to double check, did you upload this into a Workspace that has premium assigned to it (the diamond symbol)?
This was the method I used, perhaps you can give it a test.
This is the line immediately after the Source line:
#"Incremental Refresh" = Table.SelectRows(Source, each [Period Start Date] >= RangeStart and [Period Start Date] < RangeEnd),
Naturally you'd need to change "Period Start Date" to your date field.
My table uses a date key instead of a date/time date, so I had to convert. This is my filter step:
= Table.SelectRows(#"Source", each [REQUESTED_DATE_KEY] >= Date.Year(RangeStart)*10000+Date.Month(RangeStart)*100+Date.Day(RangeStart) and [REQUESTED_DATE_KEY] < Date.Year(RangeEnd)*10000+Date.Month(RangeEnd)*100+Date.Day(RangeEnd))
It seems to be the same as what you suggested but with the conversion. I am able to get to the Incremental Refresh screen. I publish it, refresh it, and it is still filtered to the 5 days that I used as defaults in the RangeStart and RangeEnd parameters.
Take a screenshot of the incremental refresh screen, the one i posted. I'm curious if something there might give me a clue.
Here it is. One difference I noticed was that mine says "Store rows in the last:" and yours actually uses the column name.
I think you have hit on the issue. In my successful attempts, the data column was exactly that. A column that just held Dates or DateTimes. Looking at your original post, you have a fairly complex formula trying to build a date and do some logic around it.
Is it possible to have or create a date column, then apply your RangeStart and RangeEnd similar to the code I provided?
Hi,
I am facing the exact same problem. I am using the same query as yours, but the default parameters are still not overwriten.
Can anyone help with this?
Many thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.