The measure you provided a while back to determine min sales dynamically (Determine the min of measure - #9 by EnterpriseDNA) works great, however, we’re running into volume that’s causing us to hit premium capacity per-query memory limits (P1 SKU, default limit equates to roughly 6144MB).
More details: Resource Governing: This query uses more memory than the configured limit. The query — or calculations referenced by it — might be too memory-intensive to run. To run this query, you could simplify the query or its calculations, or reach out to your capacity administrator to see if they can increase the per-query memory limit. More details: consumed memory 6679 MB, memory limit 6144 MB. Learn more, see https://go.microsoft.com/fwlink/?linkid=2159752.
Any ideas how to make this measure less resource intensive? I don’t think we want to adjust the query memory limit in this situation. Regarding the data set, there are roughly 40K products and 1K stores. Fact table contains roughly 300M rows.
Min Total Sales 3 =
IF (
ISINSCOPE ( DimProduct[Product] ),
VAR LastVisibleDate =
SELECTEDVALUE ( dimDate[Date] )
VAR ProductDateSales =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
FactSalesFcst,
FactSalesFcst[Product],
FactSalesFcst[Date]
),
“@Total Sales”, [TotalSales]
),
ALLEXCEPT ( FactSalesFcst, DimProduct[Product] )
)
VAR PastDates =
FILTER ( ProductDateSales, FactSalesFcst[Date] <= LastVisibleDate )
VAR Result =
MINX ( PastDates, [@Total Sales] )
RETURN
Result
)
@_mk7 Back then I gave the code based on my own experience in optimization, unfortunately I can’t tell why this code is slow unless you provide a PBIX file.
@AntrikshSharma Here’s a sample .pbix with similar measures and logic to the one experiencing the performance issue. High-level, the slider filters control the data returned in the bar charts and table. The sample contains about 70K rows whereas the real deal contains 300M.
@_mk7 Your code is slow because of the measures that you are using in the Filters pane, you have used the code shared earlier in a row context inside an iterator, which is making it difficult for the DAX engines, since this requires dedicated time, it is not possible to resolve this.
DEFINE
VAR __DS0FilterTable =
FILTER (
KEEPFILTERS ( VALUES ( '% Dif Slider'[% Dif] ) ),
'% Dif Slider'[% Dif] = 1
)
VAR __DS0FilterTable2 =
FILTER (
KEEPFILTERS ( VALUES ( 'Unit Dif Slider'[Unit Dif] ) ),
'Unit Dif Slider'[Unit Dif] = 1000
)
VAR __DS0FilterTable3 =
FILTER (
KEEPFILTERS ( VALUES ( 'DimItem'[ITEM] ) ),
NOT ( ISBLANK ( 'DimItem'[ITEM] ) )
)
VAR __DS0FilterTable4 =
FILTER (
KEEPFILTERS ( VALUES ( 'DimStore'[STORE] ) ),
NOT ( ISBLANK ( 'DimStore'[STORE] ) )
)
EVALUATE
FILTER (
KEEPFILTERS (
SUMMARIZECOLUMNS (
'DimItem'[ITEM],
'DimWeek'[ADJ_AD_WEEK_START_DT],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
"Min_Total_Sales__ITEM_", 'Key Measures'[Min Total Sales (ITEM)],
"Pct_Dev_from_Min__Outlier_Perc_Filter_", IGNORE ( 'Key Measures'[Pct Dev from Min (Outlier Perc Filter)] ),
"Forecast__Outlier_Units_Filter_", IGNORE ( 'Key Measures'[Forecast (Outlier Units Filter)] )
)
),
AND (
NOT ( ISBLANK ( [Pct_Dev_from_Min__Outlier_Perc_Filter_] ) ),
NOT ( ISBLANK ( [Forecast__Outlier_Units_Filter_] ) )
)
)
Time taken in preparing the filter context from the slicer:
I see. Thanks for digging into this. I had been reviewing in Performance Analyzer but not Dax Studio. Your screenshot is helpful.
Is there a recommended alternative approach to accomplish the same results?
Could a potential workaround be applied at the page level to prevent the visuals from rendering until a filter condition (e.g., selection of COACH_NM slicer filter) has been made to limit the data needed to iterate through?
Hi @_mk7, based on @AntrikshSharma’s response, it looks like there is no possible solution to your original query. Kindly tag this post as solved and create a separate post about the new issue presented to create more visibility. Thanks!
