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, 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) 🙂Hi TotalAnonymous , Thank you for reaching out to the Microsoft Community Forum.
When you use a native DAX query in Power Query against Analysis Services, you're submitting a direct command to the SSAS engine, like a native SQL query, bypassing the usual metadata layer and returning a fixed result set defined by your DAX. So yes, you’re correct, it’s effectively a "native query" for SSAS. In Import mode, Power BI must fully load the entire result into memory during refresh with no lazy loading, which is why your query hits the 40,960 MB memory limit, likely due to high cardinality from SUMMARIZECOLUMNS and complex measures (e.g., [Net Secondary Quantity - Invoiced (EA)] + SUM(...)) across large tables like 'Secondary Sales Invoices'.
Switching to Live Connection mode keeps query execution on the Analysis Services server, potentially bypassing the memory limit since Power BI doesn’t load data upfront—test this first by switching to Live Connection and monitoring performance with SELECTCOLUMNS(TOPN(1000, 'Secondary Sales Invoices'), "Product SKU Code", 'Secondary Sales Invoices'[Product SKU Code], "Invoice Date", 'Secondary Sales Invoices'[Invoice Date]).
Adding SharePoint data complicates things, as Live Connection works only with Analysis Services, requiring DirectQuery or Import mode for SharePoint; mixing modes is supported via composite models, but DirectQuery may slow queries, especially with large or unstructured SharePoint data, causing latency when merging results. Start with a small, filtered SharePoint dataset in DirectQuery, link it to Analysis Services via “Manage Relationships,” and optimize by pre-filtering SharePoint data and using model measures directly to avoid complex cross-source DAX.
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 :). The SharePoint dataset that I want to incorporate with Analysis Service is a Product Master/Mapping that has different structure/column with the Product Master in Analysis Service. The Sharepoint Product Master have 4 columns.
If I change the mode into Direct Query and link this product master to Analysis Service table (Product Master Sharepoint to Product Master Analysis Service (1:1 Relationship)), is it optimized enough? Also, could you please explain more about "Using Model Measures" that you've mentioned?
Thank you