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, Cookistador
Thanks for your feedback. Really appreciate it.
In this case, I use the DAX query as follows:
EVALUATE
SELECTCOLUMNS(
SUMMARIZECOLUMNS(
'Date'[Date],
'Customer'[Distributor Code],
'Store Invoice'[Channel Local],
'Product'[Product SKU Code],
FILTER(
'Date',
'Date'[Year] IN {YEAR(TODAY()), YEAR(TODAY()) - 1}
),
FILTER(
'Management Responsibility Area',
'Management Responsibility Area'[MRA Code] IN {"IID0201", "IID0301"}
),
FILTER(
'Secondary Sales Invoices',
'Secondary Sales Invoices'[Invoice Status] IN {"I - Confirmed", "S - Invoiced", "Unknown"}
),
"Total Quantity (EA)", [Net Secondary Quantity - Invoiced (EA)] + SUM('Secondary Sales Invoices'[Quantity Sellout Return Eaches]),
"Discount",SUM('Secondary Sales Invoices'[Amount Sellout Discount]) + SUM('Secondary Sales Invoices'[Amount Return Discount]),
"IMS", [Net Secondary Amount - Invoiced] + [Total Returns Amount]
),
"Date", [Date],
"Distributor Code",[Distributor Code],
"Channel", [Channel Local],
"Product SKU Code", [Product SKU Code],
"Total Quantity (EA)", [Total Quantity (EA)],
"Discount", [Discount],
"IMS", [IMS]
)
In this case, I use Selected Columns function and only choose the 7 columns. All the columns are relevant for this one fact table. This issue happens in Power Query not in Visuals....