Forum Discussion

gauravg3's avatar
gauravg3
Helper I
4 years ago
Solved

Static and Dynamic Data for report

Hi,

I have a trend report for incidents reported in last 12 months. The volume per month is >30,000 on an average.

As a result the data size is getting huge.

So, want to check if its possible to keep 11 months of data static, and only keep just last 1 month data as dynamic? 

Thanks

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi gauravg3 ,

     

    According to your statement, I know your data source is Servicenow. It is a web data source.  Incremental refresh is a good way to load data in dynamic date range. However web data source doesn't support query folding, so incremental refresh may show bad performance due to large size of data.

    For reference: Sources that support folding

    Here I suggest you to do some transform in Power Query Editor.

    Filter static table in the previous 11 months.

    Then use Group BY to get aggregate values.

    Filter dynamic table by This Month.

    You may refer to this blog to learn more details about how to optmize your report performance.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    You can filter the dataset in Power Query to load the last 12 months only

    • gauravg3's avatar
      gauravg3
      Helper I
      4 years ago

      Hi, sorry should have explained in a more clear way.

      Thing is I am extracting data from Service Now and using power automate to get the data in Power BI (via Outlook). Since the data size is getting higher, data is not getting attached to outlook.

      As the count for last 11 months would remain unchanged, so was thinking if within Power BI , is there a way to add those values as numbers (may be in table, or something similar) and only apply the query to get the data for last 1 month; this way the data size would reduce to only 1 month data. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi gauravg3 ,

         

        According to your statement, I know your data source is Servicenow. It is a web data source.  Incremental refresh is a good way to load data in dynamic date range. However web data source doesn't support query folding, so incremental refresh may show bad performance due to large size of data.

        For reference: Sources that support folding

        Here I suggest you to do some transform in Power Query Editor.

        Filter static table in the previous 11 months.

        Then use Group BY to get aggregate values.

        Filter dynamic table by This Month.

        You may refer to this blog to learn more details about how to optmize your report performance.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.