Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
After updating my on-premesis data gateway one of my scheduled report refreshes is failing with a SQL Server error "An expression services limit has been reached". That error typically indicates a SQL query is too complex. The report refresh has been working without a problem for 18 months and immediately after applying the Oct. 2020 gateway update the error started to appear. My previous gateway version was quite old (June 2019).
The report connects to SQL Server 2012. The query (View0) is shown below . The 3 underlying queries (View1, 2 & 3) are simple, they query another view (View4) which in turn each query another view (View5). View5 does a union query on 2 other views (View6 & 7). View6 is simple with only 1 table and a few filters. View7 is complex, it queries 15 tables with multiple filters and case statements, some of which contain sub queries.
So, why would this suddenly cease working when the only thing that changed was the gateway version? Microsoft recommend simplyfing the query but View0 works fine in SQL Management Studio - it is only PowerBI that can't complete the task.
View0
SELECT ROW_NUMBER() OVER (ORDER BY MONTHS_ON_LIST DESC) AS PLACE_ON_LIST, * FROM View1 UNION
SELECT (ROW_NUMBER() OVER (ORDER BY MONTHS_ON_LIST DESC, PRIORITY ASC)) + 50000 AS PLACE_ON_LIST, * FROM View2 UNION
SELECT (ROW_NUMBER() OVER (ORDER BY PRIORITY ASC, MONTHS_ON_LIST DESC)) + 90000 AS PLACE_ON_LIST, * FROM View3
Solved! Go to Solution.
I had about 40 transformations applied to the data in PowerBI Desktop, mostly Replace() methods. Reduce this to under 32 eliminates the problem. So, whatever way Microsoft send the DAX to SQL Server seems to have changed between gateway versions. I ended up moving all my Replace() methods out of PowerBI and into SQL Server and the problem is resolved.
I had about 40 transformations applied to the data in PowerBI Desktop, mostly Replace() methods. Reduce this to under 32 eliminates the problem. So, whatever way Microsoft send the DAX to SQL Server seems to have changed between gateway versions. I ended up moving all my Replace() methods out of PowerBI and into SQL Server and the problem is resolved.
Hi @cymorg ,
What's your version?Is it 3000.63.6?
Try to download the latest version via below link:
https://www.microsoft.com/en-us/download/details.aspx?id=53127
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
This error only started to happen after installing the latest version, 3000.63.6 (October 2020 (Release 2)). It's disappointing that an "upgrade" would cause such a problem, it means I may have to revert to using temporary tables which is more work with no additional value.
Hi @cymorg ,
If so,I would suggest you create a support ticket via below link and out relevant personnel will help to handle it.
https://powerbi.microsoft.com/en-us/support/
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.