Forum Discussion
Data Set above one million with filter
Hi Community,
I connected to an Azure hosted Database, the table I would like to built a report on has more then 1 mio entries. So I used a filter to restrict the result set. But seems like PowerBI Web-Based reporting still doesnt likes the requests and shows errors.
Couldn't retrieve the data for this visual. Please try again later.
Please try again later or contact support. If you contact support, please provide these details.
Thanks for your support.
Regards,
Tobi
4 Replies
tobiasgrossmann Where are you doing the filtering? I would assume Power BI, try building a view in the DB to return only the filtered result set.
Hi tobiasgrossmann,
Can you please share some detail content about your issue?
For example:
Datasource type.
Connect mode.
Create on service or desktop.Operation steps.
If you create report at desktop, you can try to use Eno1978's solution to cut down the size or use the summary data as the visual source.
If you use direct query, you can take a look at below article about direct query limitation:
Use DirectQuery in Power BI Desktop
There is a 1 million row limit for returning data when using DirectQuery. This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.
Regards,
Xiaoxin Sheng
Hi Xiaoxin ,
- Datasource type: Azure SQL Server
- Connect mode: Direct Query
- Create on service or desktop: PowerBI Cloud Version (not the desktop one)
- Operation steps: Select a large table (over 3 Mio), add a Filter that sets the result-set to less then one mio, run and see the exception.
Now as I read that we even have the one mio restrictions on the desktop version in addition, swapping to this wont be a solution :-( Is there any way to order a change for this from microsoft? Like paying more for that function?
Or shall we use alternatives to PowerBI? Anything available from Microsoft, preferable in the cloud to solve our Buisness-Case?
Regards,
Tobi
Hi tobiasgrossmann,
If you try to cut down your datasource size(100~200) and test to publish again, Does this issue appears again?
If it appears, it means the issue not related to your data size, please double check other settings.
Regards,
Xiaoxin Sheng
