Issue with "Fetching Data" in Power BI Paginated Report

[Anonymous]

Hello everyone,

I am currently working on a Power BI Paginated Report. I have built this report with a selection filter. For example, you need to select a project first before accessing more information.

Once you make a selection and click the "View Report" button, the screen shows "Fetching data" (see the attached image). The row count keeps increasing to a large number, but I don’t end up seeing the data.

Does anyone know what might be going wrong here?

Looking forward to your help, and thank you in advance!

Kind regards,

Lisanne

[Anonymous]

Hello,R1k91 ,divyed and hackcrr ,thanks for your concern about this issue.

Your answers are excellent!
And I would like to share some additional solutions below.
Hi,@Anonymous and @MikePolamalu43.I am glad to help you.
I've created a simple demo below to answer your questions separately.

I created a semantic model data source (direct connection to Power BI Desktop's report model)
This is essentially no different than connecting to a semantic model on Power BI Service

Using Query Designer to create a query statement for the semantic model data source for the monitoring report, it runs successfully here, but you can see that hardcoding is used (the condition that the field is not equal to zero is written in the query)

First test whether the data can be displayed properly, I created a table visual display of all the data
And the report is functioning properly.

Create a parameter instead of 0 in the query (instead of hardcoding)
Set the default value of 0 for the parameter to avoid manual filtering by the user and set the parameter visibility.

Set the parameter mapping first

Raw Query Statement

The strange thing here is that the system may report an error when asking you to validate. I think it's some kind of issue, but it doesn't actually affect the report running.

For DAX query in paged reports, sometimes the parameters are more complex, for example, multiple values need to be passed, and then you need to consider other approaches.
I hope the following link is helpful to you.

URL:

Solved: Paginated Report - multiple parameter via DAX quer... - Microsoft Fabric Community

2. For the original question in this post
When getting large data source data in Power BI Service, it is always in fetching data state, which is generally because the system is querying the data source data and returning it. But because the data flow is too large, it is easy to produce problems.
For Power BI paginated reports filtering, there are the following two ways

Filters :
This way in the data is retrieved to the client before the filter conditions are applied.
For example, suppose there are 10,000 pieces of data, the system will first execute a query to retrieve all 10,000 pieces of data to the client, and then filtering operations, and finally display 5,000 pieces of data.Filters are usually used for subsequent filtering when the data is displayed in the report or application.
Query Parameters:
This approach applies the filtering conditions at the data source end, so only data that meets the conditions is returned.
For example, based on the value of the Query Parameters, the system will filter directly on the database side and retrieve only the 5000 pieces of data that match the conditions.
Query Parameters can reduce the amount of data transfer from the data source to the client and improve query efficiency because only data that meets the conditions will be extracted and transferred.
The difference between the two is especially important in large data sets or data processing efficiency. Use of query parameters can reduce network load and client processing pressure

In order to avoid querying too large amount of data, I recommend you to use query parameters to check the retrieved data from the data source side, or start from the data source itself to reduce some of the duplicated data and show only some of the necessary data. (This is also a suggestion made by other users in this thread)

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[MikePolamalu43]

I have the same issue. Paginated report is fetching tens of milions of rows and I get about two hundred of them.

I've applied multiple filters in query, which returns the appropriate amount of data, however I'm trying to find out why the paginated report is "fetching" so much and how can I fix this - I'm not sure how to deal with parameters as we cannot use them with fields

I'd really appreciate your help guys!

[R1k91]

which is your sorce? SQL? Analisys Services? Power BI?

are you using parameters or filters?
you should verify if you filter is folding to the source. 

filters are executed after data is retrived Filter, group, and sort data in Power BI paginated reports - Power BI | Microsoft Learn

[MikePolamalu43]

I'm using PBI data source (workspace usage report)

I've created a query that filters out rows with 0's for report page views and defined date range to minimize the load. I've applied those filters on dataset level and page level, with the same result. Not sure what to do with parameters though

[R1k91]

I don't get what you mean by "page level", if you mean in the Power BI report the query you're sending to the semantic model doesn't really care about filters applied at the layout side.

if you use "filters" in the dataset section of the paginated report, that filter is applied after data are retrived from the source.
that means if you have 1M rows in the semantic model, that amount of data is retrived and then filtered. you should use "parameters" instead.

[MikePolamalu43]

Thank you so much for your response!

Is it possible to use parameters with fields though? My dataset consists of  columns (fields): date,report name, section name, unique user and number of page views. I've tried to define a parameter that would exclude page views different from 0 but I get an error saying that fields cannot be used in parameters.

[MikePolamalu43]

I'm using powerbi data (workspace usage stats to be precise). I used filters on a dataset level as well as on table level. I tried to create a parameter but it prevents me to use objects in parameters. Not sure what else to do 

[divyed]

Hello @Anonymous ,

This shows your query is running and fetching rows . Can you elaborate more about your dataset or query you are using and how much data you are expecting . Looks like you are trying to fetch large data . There are many option you can check/utilize :

1. Try to reduce data volume by appying query filters.

2. Your query looks slow, try to optimize query 

3. Check your output first using SSMS or any other tool to make sure you are fetching required data . Recheck your joins , filters etc.

4. Use aggregations to reduce the dataset size.

I hope I answered the question. Mark this as solution if this helps, Kudos are appreciated.

Linkedin : https://www.linkedin.com/in/neeraj-kumar-62246b26/

Cheers

[hackcrr]

Hi, @Anonymous 

You're loading too much data, resulting in data freezing.
You should try to implement query parameters for your paginated report or try to use parameters to filter out a subset of the data instead of loading it all at once.

Best Regards,

hackcrr

[R1k91]

it means the query you're running on the backend system is fetching those rows.

which is the query you're running? are you sure you filtered the dataset by parameter?