Row Filter query IS IN THE NEXT

[Ludo]

Hi,

I used "Is in the next days" to filter date and it works find until the next day.

Report or dashboard do not show the new dates for the next 7days until I refresh the queries. I am using DirectQuery.

Any Ideas.

Thanks

[v-haibl-msft]

@Ludo

I’ve reported it internally to Power BI Team: CRI 32182690

I’ll post here once I get any update about it.

Best Regards,

Herbert

[v-haibl-msft]

@Ludo

I've got response from the product team as below.

This is a known issue with Power-Query integration with Power BI in the DirectQuery mode. The source of the problem is that Power-Query transforms constant folds expressions like NOW, TODAY etc. The final Native Query which is the result of application of Power-Query transforms is what is used in the Power BI Data Model as the binding query for the table. So when a user requests next 7 days, he is basically getting a filter that explicitly uses lower bound as the current date of when the model was authored.

The easiest workaround for this issue would be the following (BTW there are other work-arounds as well):

- Right-click on the last step in the "Edit Queries" window

- Select "View Native Query"

- Copy the SQL query and create a new table against the DirectQuery backend with your own native query

- Delete the current query/table.

- For the new table you can change the current date to an appropriate function from SQL (GETDATE, GETUTCDATE, CURRENT_TIMESTAMP, SYSDATETIME, SYSUTCDATETIME, NOW).

- Then save the new table with the modified native query which does the relative date filtering appropriately

Best Regards,

Herbert

[Ludo]

Hi,

thanks for reponse.

Create new table option is grey out and i thought it is not allowed when using DirectQuery

thnks

[v-haibl-msft]

@Ludo

If the workaround is not feasible, you can try to ping the Power-Query team for a better workaround or time-line for when the issue will be addressed.

Best Regards,

Herbert