cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luissimoes Regular Visitor
Regular Visitor

Power BI Direct Query Measure Issue with 1M rows...

Currently I have a measure in Power BI supported by a direct query model. 

The formula for the measure is a simple count of records on a table filtered by another measure.

 

Expression:

Filtered Measure = CALCULATE(COUNTROWS('MyTable'); FILTER('MyTable';[Measure X]>1))

 

In PowerBI Report I am adding this measure to a Card so that I can visualize the number of records in the table.

 

[Measure X] works by its own without having any exception... but the Filtered Measure throws:

 

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

 

Looking at the generated query I can say that the output is one row with the value for the row count and yes, it is greater than 1M 🙂

 

I am wondering why the exception happens and how I can prevent it since the actual returned data is not 1M rows...

7 REPLIES 7
Community Support Team
Community Support Team

Re: Power BI Direct Query Measure Issue with 1M rows...

HI @luissimoes,

 

I think you can follow officiate document to work through the issue, but it will affect the performance.

Limitations of DirectQuery

There are currently a few limitations to using DirectQuery:

  • All tables must come from a single database

  • If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor

  • Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model

  • Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) are not supported in DirectQuery mode.

  • By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph for more information

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

To ensure that queries sent to the underlying data source have acceptable performance, limitations are imposed on measures by default. Advanced users can choose to bypass this limitation by selecting File > Options and then Settings > Options and settings > DirectQuery, then selecting the option *Allow unrestricted measures in DirectQuery mode**. When that option is selected, any DAX expression that is valid for a measure can be used. Users must be aware, however, that some expressions that perform very well when the data is imported may result in very slow queries to the backend source when in DirectQuery mode.

Use DirectQuery in Power BI Desktop

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
luissimoes Regular Visitor
Regular Visitor

Re: Power BI Direct Query Measure Issue with 1M rows...

Hello @v-shex-msft,

I already have unrestricted measures with direct query ON...

Referring to the bullet:

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.

 

My dataset is not returning more than 1 million rows... it is a simple count measure for a filtered table...

 

I guess there must be something happening in between that is invalidating this. Would be interesting to debug it.

Community Support Team
Community Support Team

Re: Power BI Direct Query Measure Issue with 1M rows...

Hi @luissimoes,

 

I find your have invoked other measure as the condition for filter, I'm not so sure combine use multiple measures with caused the issue.(if other measure has some operation will loop all table with complex operations)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
luissimoes Regular Visitor
Regular Visitor

Re: Power BI Direct Query Measure Issue with 1M rows...

Hi @v-shex-msft,

 

The other measure is also very simple:

 

Measure X = CALCULATE(DISTINCTCOUNT('Table1'[ID]); 'Dim1'[Atribute1] = "FilteredValue")

 

 

Community Support Team
Community Support Team

Re: Power BI Direct Query Measure Issue with 1M rows...

Hi @luissimoes,

 

I found your measure calculate through multiple tables, I think the issue may related to loop calculate, count of the calculate exceed the 1M.

 

For example:
Table1, 10k + rows, dim table, 100k+, loop calculation count 10K *100K=1M

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
luissimoes Regular Visitor
Regular Visitor

Re: Power BI Direct Query Measure Issue with 1M rows...

And this is something normal and acceptable using DQ?

This is something that should be able to work properly on the product since I am not returning more than 1M rows but yes calculating using joined tables that exceed 1M rows... This scenario is very common, no one is having this kind of issue?


Thank you

Community Support Team
Community Support Team

Re: Power BI Direct Query Measure Issue with 1M rows...

Hi @luissimoes,

 

>>This is something that should be able to work properly on the product since I am not returning more than 1M rows but yes calculating using joined tables that exceed 1M rows... This scenario is very common, no one is having this kind of issue?

Actually, I still not sure which place will cause your issue.(I can't reproduce this)

I'd like to suggest you contact to power bi team to know the detailed information about the this issue.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors