Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AshwiniBende
Frequent Visitor

Result set of a query to external data source exceeded the maximum allowed size of 100000 rows

Hi,

 

I am trying to create a below measure in PowerBI desktop where I used "Direct Query" and running into "Result set of a query to external data source exceeded the maximum allowed size of 100000 rows" error. When I created that same measure in "Import" mode, it is working just fine. Is there anything I can do to make this formula work while using "Direct Query"

 

Average Page Views in a Session = VAR N =

    SUMX (

        SUMMARIZE (

            test,

            test[Id],

            "AvgPageViewsbySession", AVERAGE(test[total_pageviews])

        ),

        [AvgPageViewsbySession]

    )

VAR D =

    DISTINCTCOUNT (test[Id])

RETURN

    DIVIDE ( N, D )

7 REPLIES 7
bcdobbs
Super User
Super User

Are you able to share some example data?

 

The summarize is causing the results to be materialises with a row per test id which then needs to be sent back with the rest trying to be handled by the formula engine. 

With direct query you can't move that volume of data for a query so you need to try and push it all back to source.

 

I think we might be able to do something with averagex over values(testid) of the averages and remove the summarise and the distinct count at the end. Need to see some data to be sure though.

 

Another way to go is to create a view on the server that does the group by and connect direct to that.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Is there any way that I can share a file? I do not see any option to insert a file while replying. Here is sample data and what I am trying to accomplish.

Green arrow shows the value I am trying to get at using the DAX measure I included in my original post.

 

AshwiniBende_0-1645740611912.png

 

People normally send a link from one drive/drop box/Google drive etc. if you could that would help.

 

I'm going to bed now but will look tomorrow.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Please let me know if this is something you can access. Thanks.

https://drive.google.com/file/d/1yMK8bhNHuRrXxKxt1E5u9fJr5RqBWnYw/view?usp=sharing

 

Afraid it's asking for access permissions. Did you try the code below?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@AshwiniBende , Give this a go, the calculate forces a context transition so that it evaluates the average per id. Mathematically should do the same as your code but without the summarize. It might still not work with direct query (would have to test on a direct query connection) but am hopeful it will.

 

AVERAGEX (

   VALUES ( test[Id] ),

   CALCULATE ( AVERAGE ( test[total_pageviews] )

)



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

It isn't working, still getting the same error

AshwiniBende_0-1646085714383.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.