Filtering data before loading in Dataset from Tabular Model

Swapnil 216 Reputation points
2020-12-09T22:48:25.85+00:00

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

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
435 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,242 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lukas Yu -MSFT 5,816 Reputation points
    2020-12-10T07:08:23.417+00:00

    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 :
    46817-image.png

    46779-image.png

    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

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,411 Reputation points
    2020-12-10T07:31:06.447+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments