1.000.000 Row Question

[armvoss]

Hello,

i have a question regarding the 1 Million Row problem. We have a DirectQuerry Connection to the SQL Database. And an high cardinality of 10 different values per hour on a 24/7 schedule. When i started the project, it is our Demosystem to get some knowledge in Power bI, there where only a few values. Thing is the whole report runs fine in our Teams. When i click on it i can see the line chart.

But when i trie to make a new report in Power BI DESKTOP to the same database, i get the 1 Million Row error. If i apply a slicer, narrow it down on a small date, and configure the linechart. i can see the line chart in Power BI DESKTOP.

Will i run into some troubles in the future whith DirectQuerry regarding the rows? 

Is this behavior in the Service and Power BI Desktop the same? If a querry in Power BI Service gives more than 1 Million rows will it show an error? Why does the DirectQuerry report still work in Service, if a select the start date of the Project until today? it should give over 1 Million rows as a result?

thanks for the Answer and the help

[lbendlin]

Same limitation.  Think of it from the other side. What do you need 1M rows for? Can you answer your business question with aggregated data (obtained via query folding, for example)?

[armvoss]

Hello lbendlin:

i can't aggregate the data. The data in question has a high cardinality. As mentioned in my first post. Per day 240 values, 7 days a week. So in 1 Month 7.2000 values. In 1 Year 86.400 values. So in 11 years 950.400 distinct values. Sometimes data is recorded every 5 min, distinct values.

So i understand there is a limitation of 1M rows as a result. that's ok. But currently my table in question has over 2M rows. So my question remains: Why can power BI Display all values over the whole project and doesn't show me an error? I get this little "i" which states in short:

this is a sample data. Filter your data.

you can deactivate the sample algorythm in the general settings pane of the visual. But the info about the sample presentation of my visual will still show that this is a sample.

So as i understand it: Power BI has a limitation of 3.500 datapoints in a linechart. Understood.

But how can it display, if i go over the whole runtime with over 2.000.000 rows?

[lbendlin]

What's your reason for using Direct Query? Would Import Mode be more appropriate? Incremental Refresh even? These options don't have the 1M rows limit.

[armvoss]

Because we would like to have the newest values. If you go for Import i can schedule a refresh every 30 min for the whole day (48 times). This would be ok if new data is generated every hour or half hour. If the scenario requires that the data is generated every 5 min, than i have a problem. But again my question still is: why does it work? there are more than 1 million rows in database. in power bi service, if i select the whole time i get the result. it id working in direct conection with 2.000.000 rows. why?

[lbendlin]

Looks like you want to consider Incremental Refresh - It allows you to keep the latest partition (day) in direct query mode and everything else in import mode.

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn