Data Set above one million with filter

[tobiasgrossmann]

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.

Activity ID8c498b87-466e-057e-7549-412f06e05217

Request IDff91b58a-1407-bba5-bf60-635cc0e2f776

Correlation IDca6221fa-eb91-6ff8-7e9c-71b041fb1eef

TimeMon May 22 2017 17:34:05 GMT+0200 (W. Europe Daylight Time)

Version13.0.1700.2227

Cluster URIhttps://wabi-north-europe-redirect.analysis.windows.net

Thanks for your support. 

Regards,

Tobi 

[Anonymous]

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

[tobiasgrossmann]

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

[Anonymous]

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

[Seth_C_Bauer]

@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.