Paginated Report fetching all data when using a parameter

[mecrbpower]

Hi

I have created a Power BI dataset which shows all sales invoices for the last number of years. The Power BI report uses SQL query. I am retrieving approx 50 columns and can have approx 200,000 records of data. The Power BI query and visuals within the Power BI report work fine with no performance issues.

From this dataset I have created a paginated report. The report will be used to display an actual sales invoice. The report passes in a paramater of Invoice ID and then display the actual sales invoice.

This all works fine except it is very slow. 

When I enter the Invoice ID and 'View Report', the report shows the 'Fetching Data' window. It reads through all the 200,000 records each time I run the report and then dislays the actual invoice data(paginated report).

I would imagine this is not correct and that I can somehow but a key on the data and retrieve the required invoice data quicker.

Many thanks for your help

Michael

[JulsC]

Hi, did you get a solution for your problem as I am experiencing the same.  I can see you accepted the change from ImportQuery to DirectQuery but I already use DirectQuery with the same issue?

[mecrbpower]

Hi Fen

Many thanks for your reply.

I am not using SQL in the paginated report. I am using a Power BI Dataset. And then using a filter on the data.  I will try and show what I have setup.

I have 2 Datasets in the report. See my Dataset properties

Dataset = InvoiceParameter

I use this in my parameter dropdown to show all invoices

Dataset = Dataset1

This retrieves all the required data. I use a filter on this Dataset

Filter on this Dataset

The report works fine in that I can select the Invoice from the parameter and the correct invoice details are returned to the report. The issue is performance. Each time I run 'View Report' the report it fetches all the records.  See window, it counts through all the rows sequentially, so If I have 200,000 rows it fetches 200,000 records

thanks again for your help

regards

Michael

[Anonymous · Accepted solution]

Hi, @mecrbpower 

I'm not sure if I understand correctly: do you mean that your final result can be filtered with the correct data by the parameter, but before filtering the data it will load all the data resulting in lower performance?

May I ask what connection mode your semantic model is using to connect to SQL Server? If you are using import mode, I suggest you to recreate a new semantic model using DirectQuery mode.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mecrbpower]

Hi Fen

Thanks and really appreciate your input. I will try and explain more clearly what my issue is.

I have a Power BI report that retrieves the data from my ERP system. I use Direct Query within the Power BI report.  I have then published this report in Power BI service.  There are no performance issues on the Power BI report.

Then I create my paginated report over this dataset of data.  The report is to accept as input a parameter of Invoice ID.  When the report is run ‘View Report’ the invoice details for the passed invoice ID is displayed.  This is all correct and working OK.

The issue is performance.  When I Run the report it fetches ALL records from the dataset, it seems to be reading sequentially through each row of data. How can I stop the report fetching all records, only fetch the one record I am looking for.

regards

Michael

[Anonymous]

Hi, @mecrbpower 

I am glad to help you.

Please check if you have bound the parameter to the SQL query:

First, you can try to check your SQL query where you are using Invoice ID parameter directly. This way, the database will only fetch the relevant records:

SELECT
  SalesInvoices.InvoiceID
  ,SalesInvoices.[Values]
FROM
  SalesInvoices

Where SalesInvoices.InvoiceID IN (@InvoiceID)

Result:

Of course you can also use the Invoice ID parameter without adding a Where clause to the SQL statement, but instead bind the parameter in Filters in the dataset for filtering:

Result:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.