Forum Discussion
Error Querying from Analysis Services Source
Hi TotalAnonymous , Thank you for reaching out to the Microsoft Community Forum.
Yes, it is optimized enough for your scenario. With a small dataset, performance should be efficient if you use a unique key in “Manage Relationships” and pre-filter the SharePoint data in Power Query to remove unused rows. Test this with SELECTCOLUMNS(TOPN(1000, 'Product Master'), "Product ID", 'Product Master'[Product ID], "Name", 'Product Master'[Name]) and monitor refresh times. If latency occurs, importing the SharePoint table or enabling incremental refresh on Analysis Services with RangeStart/RangeEnd (admin action) can further optimize.
“Using model measures” means utilizing pre-defined calculations in your Analysis Services model, like [Net Secondary Quantity - Invoiced (EA)], which you can find in the Power BI Fields pane. Instead of writing new DAX, drag these measures into visuals to leverage server-side processing. This avoids cross-source DAX issues with SharePoint, ensuring efficiency. If needed ask your admin to add measures like TotalQuantityAggregated := CALCULATE(SUM('Secondary Sales Invoices'[Net Secondary Quantity - Invoiced (EA)]), ALL('Date')).
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi TotalAnonymous , Thank you for reaching out to the Microsoft Community Forum.
“This query uses more memory than the configured limit" (40,960 MB) indicates your DAX query exceeds the per-query memory cap during Power Query import, likely due to high data volume and complex calculations.
Please try below:
Replace the date filter in SUMMARIZECOLUMNS with below then test and expand gradually if successful.
FILTER('Date', 'Date'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 3, 1))
Ask your admin to create an aggregated table (Adjust filters as needed)
TotalQuantityAggregated := CALCULATE(SUM('Secondary Sales Invoices'[Net Secondary Quantity - Invoiced (EA)]) + SUM('Secondary Sales Invoices'[Quantity Sellout Return Eaches]), ALL('Date'))
Process one MRA at a time, run separate queries and combine in Power BI. Example:
FILTER('Management Responsibility Area', 'Management Responsibility Area'[MRA Code] = "IID0201")
Run this to isolate the issue: EVALUATE TOPN(1000, SELECTCOLUMNS('Secondary Sales Invoices', "Product SKU Code", 'Secondary Sales Invoices'[Product SKU Code], "Invoice Date", 'Secondary Sales Invoices'[Invoice Date]))
Start with the 3-month date range test. If it works, scale up while monitoring memory. If issues persist, collaborate with your admin to pre-aggregate data. If needed, request your admin to raise the memory limit
If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.
Hi, v-hashadapu ,
Thanks for your detailed explanation. Please allow me to check and get back to you ASAP 🙂Hi TotalAnonymous , Happy to help. Please take your time and check if it works. If it doesn't work, please share the details. Thank you.
Thanks v-hashadapu. Please wait...
I'm just curious because this DAX query is like Native Query right for Analysis Service, am I correct? however, this kind of issue really challenging. I'm thinking to switch into Live Connection mode but since I have another source from SharePoint and want to add it, I'm afraid it will affect the performance due to connection mode switching (Live Connection -> Direct Query) 🙂
