Percentile Calculation

[Gayathri999268]

Hi All,

I am facing an issue with percentile calculation in Power BI. I need to have Total, Average and percentile values of a column in Power BI with direct query mode. Total and Average are working fine. But when it comes to percentile I am getting an error saying 'The result set of a query to external data source has exceeded the maximum allowed size of '1000000' rows'.

My backend table is having more than 1M records. The total and average on the table with all records is working fine. The percentile is also working fine if I put a filter on the Measure formula to restrict records ( less than 1M). How can I fix this issue as I need to calculate the percentile for all the records.

Thanks in advance.

[Gayathri999268]

Thank you @v-janeyg-msft  for your reply.

We are using Power BI report server.  Kindly please advise if we can set any limit to row count from admin side if the report is running on report server and how to do it

And actually the measure is very simple :

Percentile_25_Sal 

= CALCULATE(PERCENTILE.INC(EMP[SALARY],0.25),EMP[ACTIVE]=1)

The row count of this filtered dataset  is greater than 1M.

Thank you

[v-janeyg-msft]

Hi, @Gayathri999268 

Sorry, I'm afraid not. The limit of DQ should be the same on desktop for service and desktop for report server. I think the only thing you can do is to control the results returned by the query to be less than 10 million rows. After all, premium is paid, so it is powerful.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Gayathri999268]

Thank you for the reply @v-janeyg-msft .

Unfortunately I cannot reduce the dataset size as per the client requirements. Just for my understanding to proceed ,Could you please clarify how to set this limit incase we acquire a premium license for Power BI Report server.

Thanks in Advance.

[v-janeyg-msft · Accepted solution]

@Gayathri999268 

The premium license can't be used for report server, it is used in pbi service. And admin can modify capacity setting in service with may be meet your needs. But I can’t be 100% sure because I don’t know your specific situation. If you have(If your report server key is obtained from premium), you can try to publish the report to service, add a gateway, and see if it can be displayed.

Reference:

Power BI Premium new capacity settings allow for more control over datasets | Microsoft Power BI Blo...

What is Microsoft Power BI Premium? - Power BI | Microsoft Docs

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

Hi, @Gayathri999268 

You can check the limitation:

For example, you can aggregate 10 million rows with your query that runs on the data source. The query accurately returns the results of that aggregation to Power BI using DirectQuery if the returned Power BI data is less than 1 million rows. If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit).

For more details, you can see the below:

Use DirectQuery in Power BI Desktop - Power BI | Microsoft Docs

For your situation, If you have premium capacity, It may be displayed normally on the service. If not, I'm afraid it's hard to do.

What results do you want to show? You can share the measure formula, let’s see if it is possible to simplify it.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey