Forum Discussion
Helper IGet data for 1 week before depend on TODAY()
Hi MichaelJackpbi,
In Query Editor, Choose your date column -> click filter icon in top right of header of column -> choose Date Filters -> Custom Filter -> Fill in This quarter and Last quarter with OR operator as below pictures:
(Every actions have been done by UI - love it )
Back to your speed question, depends on your query(or amount of data) the loading time will be fast or slow. That means it will take more time when loading data for quarter than week. And i hope you have good index stategy in your sources. it's one important factor.
- Import mode: loading all data of your query - one time when refreshing - good for UX
- Direct Query: loading data based on user's interaction (user could choose an option in slicer -> system generates the query corresponding and just loads the data of that query) - user has to wait when loading huge data of that action or long distance of data center to user's location
Helper IGilbertQ you mean i have load all of data from database to power bi then filter as my rules! there are 65m rows in database and i just need to get 1 week only- around 2-3m..
Memorable MemberHi MichaelJackpbi,
Some basic queries of Power Query(M Language) in Query Editor will be executed in your data source and return result without loading all data.
E.g: I will filter FullDateAlternateKey in this month by power query as below pictures
So if you have large/big data, you should consider to filter data in Query Edit before using DAX. Please try your case with Date.IsInCurrentWeek method for getting data of this week.
To understand more about this solution, you could search about Query Folding concept
And if you get the issue when loading all data with Importing mode, you could switch to DirectQuery Mode (it will load data based on user's behaviors)
If this works for you please accept it as solution and also like to give KUDOS.
Best regards
Tri Nguyen
- MichaelJackpbi
Hi tringuyenminh92 It souunds great ! But i still confuse about the data loading speed, may the edit effect to the speed? in case i would keep the rows for 2 Quarters - current Quater & Last Quater, how to do that?
Thanks,J.
- tringuyenminh92
Hi MichaelJackpbi,
In Query Editor, Choose your date column -> click filter icon in top right of header of column -> choose Date Filters -> Custom Filter -> Fill in This quarter and Last quarter with OR operator as below pictures:
(Every actions have been done by UI - love it )
Back to your speed question, depends on your query(or amount of data) the loading time will be fast or slow. That means it will take more time when loading data for quarter than week. And i hope you have good index stategy in your sources. it's one important factor.
- Import mode: loading all data of your query - one time when refreshing - good for UX
- Direct Query: loading data based on user's interaction (user could choose an option in slicer -> system generates the query corresponding and just loads the data of that query) - user has to wait when loading huge data of that action or long distance of data center to user's location