Filtering data before loading in Dataset from Tabular Model

Question

Hi All,
I have created a Tabular Import Model with last 5 years of data which has approx 10 Miliions of rows which is required for trend reporting.

Now I am trying creating a Power Bi Desktop report for last say last 7 days, where I have Date dimension with Flag Last 7 Days with values as Y and N.

Now if I try creating report by connecting model , I need to have a filter created which would automatically filter data for last 7 Days by just selecting Y (ignoring all rows values with N in Last 7 Days column in Date dim)

But when I connect to Model I need to select Last 7 days -> Transform Load ->and then Select Y in Last & Days column which is time consuming. Then it would filter only last 7 days data from last 5 years data of model.

Can we create such predefined Date Filters in Tabular Model so that just selecting it would filter data in model? Any workaround also welcome here.

Thanks

Answer 1

Hi,

I guess you want to add the filter in the Tabular Model table query, right ? So that , when the user or report connect to the model, the data is already filtered to last 7 days.

You could in the model, in table properties, change the Power Query to add the filter. For example :

The query would be like :

let  
    Source = #"*YourDataSource*",  
    dbo_DimProduct = Source{[Schema="dbo",Item="Table"]}[Data],  
    #"Filtered Rows" = Table.SelectRows(YourTable, each ([Date] > **Date.AddDays(DateTime.LocalNow(), -7**))))  
in  
    #"Filtered Rows"  

You may need to change above code to suit your model.
You could also click the design button to make the query in the Power Query Editor.

Reference : Date.AddDays

Regards,
Lukas

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
What can I do if my transaction log is full?--- Hot issues November
How to convert Profiler trace into a SQL Server table -- Hot issues November

Answer 2

Conceptually, your Tabular model needs to support both 5-year and 7-day analyses (or possibly for other periods as well). When a new connection is made, the model has by itself no way of knowing whether this connection is for 5 years of data or for 7 days, so there has to be some amount of time spent on passing this user's choice to the model. Your choice as a developer is who and how does this work for each report off the model. For example, in Power BI that you mentioned there's a relative date slicer specifically designed for the 'last N periods' use case.