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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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
Community Champion
Community Champion

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!!
Anonymous
Not applicable

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!!
Anonymous
Not applicable

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!!
bcdobbs
Community Champion
Community Champion

@Anonymous , 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!!
Anonymous
Not applicable

It isn't working, still getting the same error

AshwiniBende_0-1646085714383.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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