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 :). 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
Thanks for your answer! I will mark it as solution first. There is another option, which is using Virtual Machine when open up Power Query. Our admin said that instead of open Power Query in desktop, I need to try open it in Virtual Machine. I'm trying right now. Hopefully, it can solve it.
Hi TotalAnonymous , Thanks for the update. Hope it works for you.