Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 )
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.
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.
Please let me know if this is something you can access. Thanks.
https://drive.google.com/file/d/1yMK8bhNHuRrXxKxt1E5u9fJr5RqBWnYw/view?usp=sharing
@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] )
)
It isn't working, still getting the same error
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
78 | |
59 | |
51 |
User | Count |
---|---|
164 | |
84 | |
68 | |
68 | |
58 |