DAX calculations slow - takes 847 seconds

[Thijslo]

Can someone help me improving my DAX calculations? I am quite new to Powerbi, so it could be that i am doing something 'stupid'.

I am trying to perform a MRP calculation for all our items and all dates. The stock quantity for each date is calculated by
StockQuantityExpected = Currentstock - SalesDeliveries + PurchaseReceipts + ManufacturedProducts - IssueMaterialsForManufacturing

I show an item in the matrix visual when the StockQuantityExpected is below the SafetyStock one one of the dates.

I conditional format each item/date value based on Negative/Positive value and whether it is above/under SafetyStock.

Please assist.

The calculations for one visual(matrix) takes now 848797 ms. Whereof DAX query: 847766 ms.

The matrix has conditional formatting and shows values based(filter) on a measure.

Measure 1(calculated values, used in measure 2):

StockQuantityExpected = calculate(sum(ItemWarehouses[CurrentStock]))

-calculate(sum(SalesOrderLines[QuantityToBeDeliveredFinal]),USERELATIONSHIP(Dates[Date],SalesOrderLines[DeliveryDateExpected]))

+calculate(sum(PurchaseOrderLines[NotReceivedQuantityItemUnits]),USERELATIONSHIP(Dates[Date],PurchaseOrderLines[ReceiptDate]))

+calculate(sum(ShopOrders[CurrentNotReceivedQuantity]),USERELATIONSHIP(Dates[Date],ShopOrders[PlannedDate]))

-calculate(sum(ShopOrderMaterialPlanDetails[RemainingQuantity]),USERELATIONSHIP(Dates[Date],ShopOrders[PlannedStartDate]),USERELATIONSHIP(ShopOrderMaterialPlanDetails[Item],Items[ID]))

Measure 2 (shown values in the matrix and used in measure 3):

StockQuantityExpectedCumulatief = calculate([StockQuantityExpected], FILTER( ALL(Dates[Date]), Dates[Date] <= MAX(Dates[Date]) ))

Measure 3 (used for filtering):

StockQuantityExpectedCumulatiefShowSafetyStock = SUMX((ALL(Dates)),IF([StockQuantityExpectedCumulatief]>='Key Measures'[SafetyStockSum],0,-1*[StockQuantityExpectedCumulatief]))

I copied the query via de performance analyzer here below:

// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS({"Warehouse1"}, 'Warehouses'[WarhouseDescription])

VAR __DS0FilterTable2 =
FILTER(
KEEPFILTERS(VALUES('Dates'[Date])),
AND('Dates'[Date] >= DATE(2020, 10, 22), 'Dates'[Date] < DATE(2022, 3, 17))
)

VAR __DS0FilterTable3 =
TREATAS({"Approved"}, 'SalesOrders'[ApprovalStatusDescription])

VAR __DS0FilterTable4 =
TREATAS({TRUE}, 'Items'[IsStockItem])

VAR __DS0FilterTable5 =
FILTER(
KEEPFILTERS(VALUES('ItemCorrectionInclude'[IncludeCorrectionItems])),
'ItemCorrectionInclude'[IncludeCorrectionItems] <> "No"
)

VAR __DS0FilterTable6 =
FILTER(
KEEPFILTERS(VALUES('PurchaseOrders'[SelectionCodeCode])),
'PurchaseOrders'[SelectionCodeCode] <> "P-Quote"
)

VAR __ValueFilterConstraintDM1 =
SUMMARIZECOLUMNS(
'Items'[Description],
'Items'[Code],
'Dates'[Year],
'Dates'[QuarterOfYear],
'Dates'[MonthOfYear],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
"StockQuantityExpectedCumulatief", 'Key Measures'[StockQuantityExpectedCumulatief],
"SafetyStockSum", IGNORE('Key Measures'[SafetyStockSum])
)

VAR __ValueFilterDM1 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'Items'[Description],
'Items'[Code],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__ValueFilterConstraintDM1,
"StockQuantityExpectedCumulatiefShowSafetyStock", IGNORE('Key Measures'[StockQuantityExpectedCumulatiefShowSafetyStock])
)
),
[StockQuantityExpectedCumulatiefShowSafetyStock] < 0
)

VAR __DS0Core =
SUMMARIZECOLUMNS(
'Items'[Description],
'Items'[Code],
'Dates'[Year],
'Dates'[QuarterOfYear],
'Dates'[MonthOfYear],
__DS0FilterTable,
__DS0FilterTable2,
__DS0FilterTable3,
__DS0FilterTable4,
__DS0FilterTable5,
__DS0FilterTable6,
__ValueFilterDM1,
"StockQuantityExpectedCumulatief", 'Key Measures'[StockQuantityExpectedCumulatief],
"SafetyStockSum", IGNORE('Key Measures'[SafetyStockSum])
)

VAR __DS0PrimaryWindowed =
TOPN(
101,
SUMMARIZE(__DS0Core, 'Items'[Description], 'Items'[Code]),
'Items'[Description],
1,
'Items'[Code],
1
)

VAR __DS0SecondaryBase =
SUMMARIZE(__DS0Core, 'Dates'[Year], 'Dates'[QuarterOfYear], 'Dates'[MonthOfYear])

VAR __DS0Secondary =
TOPN(102, __DS0SecondaryBase, 'Dates'[Year], 1, 'Dates'[QuarterOfYear], 1, 'Dates'[MonthOfYear], 1)

VAR __DS0BodyLimited =
NATURALLEFTOUTERJOIN(
__DS0PrimaryWindowed,
SUBSTITUTEWITHINDEX(
__DS0Core,
"ColumnIndex",
__DS0Secondary,
'Dates'[Year],
ASC,
'Dates'[QuarterOfYear],
ASC,
'Dates'[MonthOfYear],
ASC
)
)

EVALUATE
__DS0Secondary

ORDER BY
'Dates'[Year], 'Dates'[QuarterOfYear], 'Dates'[MonthOfYear]

EVALUATE
__DS0BodyLimited

ORDER BY
'Items'[Description], 'Items'[Code], [ColumnIndex]

[Thijslo]

@Anonymous ; Thank you for your notification. Doesn't make me happy, but at least information for what I can expect 🙂

Sharing all the data is a bit more complex, because there is conficential information in a lot of the data/tables.

I hoped that my calculations are somehow not logically build up, and someone could give feedback in improvements. Seems i have to contact a consultant then (who will look with me to the data).

In case anyone else still has some (general) feedback for improvements I am still interested!

[Anonymous]

// This is all I could do with the data you've provided...
// Please don't expect miracles.

StockQuantityExpected =
var CurrentStock_ = 
    SUM( ItemWarehouses[CurrentStock] )
var QuantityToBeDeliveredFinal_ =
    CALCULATE(
        SUM( SalesOrderLines[QuantityToBeDeliveredFinal] ),
        USERELATIONSHIP ( Dates[Date], SalesOrderLines[DeliveryDateExpected] )
    )
var NotReceivedQuantityItemUnits_ = 
    CALCULATE(
        SUM( PurchaseOrderLines[NotReceivedQuantityItemUnits] ),
        USERELATIONSHIP ( Dates[Date], PurchaseOrderLines[ReceiptDate] )
    )
var CurrentNotReceivedQuantity_ =
    CALCULATE(
        SUM( ShopOrders[CurrentNotReceivedQuantity] ),
        USERELATIONSHIP ( Dates[Date], ShopOrders[PlannedDate] )
    )
var RemainingQuantity_ = 
    - CALCULATE(
        SUM( ShopOrderMaterialPlanDetails[RemainingQuantity] ),
        USERELATIONSHIP ( Dates[Date], ShopOrders[PlannedStartDate] ),
        USERELATIONSHIP ( ShopOrderMaterialPlanDetails[Item], Items[ID] )
    )
var Result =
    CurrentStock_
    - QuantityToBeDeliveredFinal_
    + NotReceivedQuantityItemUnits_
    + CurrentNotReceivedQuantity_
    - RemainingQuantity_
RETURN 
    Result
 

StockQuantityExpectedCumulatief =
var LatestDateVisible = MAX( Dates[Date] )
var Result =
    CALCULATE(
        [StockQuantityExpected],
        Dates[Date] <= LatestDateVisible
    )
RETURN
    Result


StockQuantityExpectedCumulatiefShowSafetyStock =
CALCULATE(
    SUMX(
        DISTINCT( Dates[Date] ),
        var QtyExpCumul = [StockQuantityExpectedCumulatief]
        var SafetyStock = [SafetyStockSum]
        var Result =
            (-1) * (SafetyStock > QtyExpCumul) * QtyExpCumul
        return
            Result
    ),
    REMOVEFILTERS( Dates )
)

[Anonymous]

@Thijslo 

Show me your model and I'll at least be able to tell you roughly if it's correct from the point of view of Best Practices. This is of crucial importance when tuning DAX queries. No amount of tuning can make a bad model perform nicely. That's for sure.

[Thijslo]

@Anonymous Thanks fo your help above! I changed the formulas and it looks a bit faster, but indeed no miracles. But at leas I can learn form it as well!

Here is my model attached, I did my best to make it a bit organized (after the 'auto' organize function messed it up first).

In case you are interested, the API description of the tables is here.

[Anonymous]

@Thijslo 

I can immediately see a pattern that Marco Russo and Alberto Ferrari teach in their courses (e.g., Mastering DAX) as an anti-pattern. If you have something like Orders and OrderLines, that is, a summary table with a detailed table, you should remove the summary table and only keep the one with the details. Of course, you must do it in such a way to retain the necessary numeric summaries (but that's really easy with PQ and many things from the summary tables can be calculated from the details, if not all). Many reasons why this should be so but don't have enough time to explain. You could try to look for patterns on their website: www.daxpatterns.com

[Anonymous]

@Thijslo 

Sorry to say that but nobody will help you here with the information you've provided. For one very simple reason: to do DAX code optimzation one has to have access to the actual model with representative amounts of data.

So, you've got 2 choices. Either you'll wait forever for an answer... or you'll give us a file with representative data and amounts. Up to you.