Dynamic filter on YYYYMMDD in Query Editor

[Anonymous]

Hi Guys,

I need to dynamicaly filter report to keep say last 20 weeks of data from Oracle table on refresh.

(Data is snapshotted every Sunday , and is reported by Snapshot_Date_Key, Format: numeric YYYYMMDD)

We have a few large Fact tables (100 million+) and so it isn't feasbile to refresh all data every week hence only the last 20 weeks. 

(Note - we can't use incremental refresh just yet due to security concerns so need something in the meantime)

I have tried using a combo of Date.From(Text.From([SNAPSHOT_DATE_KEY]) and 'In the last 20 weeks' filter, which indeed works however this Date.From(Text.From) combo isn't supported in Query Folding. 

Can anyone think of a way of dynamic filtering on YYYYMMDD in the Query Editor?

Eg keep SNAPSHOT_DATE_KEY in the last 20 weeks from today

Thanks in advance!!

[Anonymous]

@Anonymous,

Why not directly write SQL statement during import process to limit data to last 20 weeks?





Regards,
Lydia

[Anonymous]

Hi thanks for the reply - but as soon as you use custom SQL, query folding becomes unavailable (which is something I mentioned I want to avoid)

[Anonymous]

@Anonymous，

When you use SQL statement to drag required data to Power BI Desktop, the performance is better than that you drag all the data to Power  BI Desktop and then filter the data in the last 20 weeks.

Regards,
Lydia

[Anonymous]

From what I understand:
Query folding already applies this filtering logic, as it passes filters to the data source (oracle database).

By putting custom SQL in at the start query folding becomes unavailable

[Anonymous]

@Anonymous，

From performance perspective, it is recommended to import required columns and rows in Power BI Desktop, then shape data and create report .

Regards,
Lydia

[Anonymous]

Hi @Anonymous just coming back to this... any ideas?

Note I can't import everything as the oracle database goes back many years and cannot bring in 100s of millions of rows - hence the filter!