cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thijslo
Frequent Visitor

DAX calculations slow - takes 847 seconds

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]

6 REPLIES 6
Thijslo
Frequent Visitor

@daxer ; 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!

// 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 )
)

@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
Frequent Visitor

@daxer 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 PowerBImodel.PNGmy 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.

@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

 

daxer
Solution Sage
Solution Sage

@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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors