Couldn't load the data for this visual error when using Median function

[markdileo]

I'm new to Power BI Desktop and am attempting to evaluate various features of the product for my company.  We have a large database of over 400 million records and I'm running into an issue with the Median function.

I'm attempting to construct a Table Visual using Filters, Sum, Count, and Average.  Based on the filters, the table is loading just over 3 million records.  When I add the Median function into the mix, I get the Couldn't load data for this visual error.  The reason is "The resultset of a query to an external data source has exceeded the maximum allowed size of '1000000' rows." I'm using Direct Query on a local SQL Server database.

What can I do to get around this other than importing all of the data?

Thanks!

[Anonymous]

@markdileo: See if this article helps:

https://powerbi.microsoft.com/en-us/blog/the-conceptual-data-model-and-limits/

[markdileo]

@Anonymous.  Thanks!  I did see this article, but it didn't seem to apply to what I'm trying.

[Seth_C_Bauer]

@markdileo If that is a hard limit (haven't seen it before), the only other way that I can think of would be to push your data into a tabular model (or MD) and creating a live connection. Importing would probably take awhile, and with really large data sets you wouldn't be able to publish...

[markdileo]

@Seth_C_Bauer Thanks for your reply.  I'm trying to work through various evaluation scenarios and this is the first major issue I've run into.  It appears to be hardcoded, but the only limits I've seen documented elsewhere appear to be using the service and file sizes.  I'm using Direct Query against a local SQL server (2014).