Need help to optimize DAX query

[Mohan128256]

Hello All,

I have a requirement where i need to calculate the 

sales order status from the point of a product was ordered.

the data which i have for one of the sales order id is as below for a customer.

Sample Data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdK9DkBAEATgV5GrXTJ36+doUahoVKJiC4kgV3h+p1aQ7GaameTrdhyVAUJ6PEe5ilXqXEKZboYqFOM0SFtYioASCFN38h51fmGvpvjTFzJvIfRG6O3L17ytF3te/nCS8UTGUxnPZDyX8ffntft1rPOjpxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order ID (H)" = _t, #"Product ID (L)" = _t, SnapDate = _t, #"Sales Order Status Name (H)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Order ID (H)", type text}, {"Product ID (L)", type text}, {"SnapDate", type text}, {"Sales Order Status Name (H)", type text}})
in
    #"Changed Type"

I am trying to calculate the initial sales order status and final sales order status in a measure based on the dates filters.

For this perticular sales order id, the dates are from 18-03-2023 to 28-03-2023 and the initial status is Open Order so the measure should return Open Orderv and Invoiced as final status in all rows.

But if the date range selected from 21-03-2023 to 24-03-2023 then the measures should return as below

I have tried below dax which is working fine.

Ordered Qty (L) Before = 
VAR _SalesOrderID = MAX('Sales Orders Snapshots'[Sales Order ID (H)])
VAR _ProductID = MAX('Sales Orders Snapshots'[Product ID (L)])
var _max = MINX(filter(ALLSELECTED('Sales Orders Snapshots'), 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID), 'Sales Orders Snapshots'[SnapDate]) 
return 
CALCULATE(MAX('Sales Orders Snapshots'[Sales Order Status Name]), filter(ALLSELECTED('Sales Orders Snapshots') ,  'Sales Orders Snapshots'[SnapDate] =_max && 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID))  
 

But i am facing another issue with the performance of this query.

When i do select individual sales order id or customer name from slicer the query is running fine and showing the results.

But if i dont select anythig from any of the slicer, it keeps spinnings and after waiting for almost 15 to 20min it gives an error as Resuourses exceeded try filtering to decrease the amount of data displayed.

The data which i am having in my report of this table is around 9,64,000.

Could you @amitchandak please help me to optimize this dax query.

Thanks in advance.

Mohan V.

[marcorusso]

In the previous DAX code you didn't use Sales Order Status Name, so I didn't understand the correlation with the sample report.

Final Status =
VAR _MinDate =
    CALCULATETABLE(
        ADDCOLUMNS(
            SUMMARIZE(
                'Sales Orders Snapshots',
                'Sales Orders Snapshots'[Sales Order ID (H)],
                'Sales Orders Snapshots'[Product ID (L)]
            ),
            "@SnapDate", MIN( 'Sales Orders Snapshots'[SnapDate] )
        ),
        ALLSELECTED()
    )
VAR _FilterSnap =
    TREATAS(
        _MinDate,
        'Sales Orders Snapshots'[Sales Order ID (H)],
        'Sales Orders Snapshots'[Product ID (L)],
        'Sales Orders Snapshots'[SnapDate]
    )
VAR _Result =
    CALCULATE(
        MAX( 'Sales Orders Snapshots'[Sales Order Status Name (H)] ),
        ALLEXCEPT(
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        ),
        KEEPFILTERS( _FilterSnap )
    )
RETURN _Result

[Mohan128256]

Hi @marcorusso 
the above DAX is giving the required result but  the visual is taking more rendering time when we use this measure .
i would request you to provide more optimiszed version of the above DAX query.

[marcorusso]

So you want this?

Ordered Qty (L) Before = 
VAR _FilterSnap =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Sales Orders Snapshots',
                'Sales Orders Snapshots'[Sales Order ID (H)],
                'Sales Orders Snapshots'[Product ID (L)]
            ),
            "@SnapDate", MIN ( 'Sales Orders Snapshots'[SnapDate] )
        ),
        ALLEXCEPT (
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Sales Orders Snapshots'[Ordered Qty (L)] ),
        ALLEXCEPT (
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        ),
        KEEPFILTERS ( _FilterSnap )
    )

[Mohan128256]

@marcorusso I really appreciate your efforts here but unfortunately these queries are not giving the right result which i am expecting as mentioned in my above question.

Let me take you the ask here once again.

Sample Data which you can check here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdK9DkBAEATgV5GrXTJ36+doUahoVKJiC4kgV3h+p1aQ7GaameTrdhyVAUJ6PEe5ilXqXEKZboYqFOM0SFtYioASCFN38h51fmGvpvjTFzJvIfRG6O3L17ytF3te/nCS8UTGUxnPZDyX8ffntft1rPOjpxs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order ID (H)" = _t, #"Product ID (L)" = _t, SnapDate = _t, #"Sales Order Status Name (H)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales Order ID (H)", type text}, {"Product ID (L)", type text}, {"SnapDate", type text}, {"Sales Order Status Name (H)", type text}})
in
    #"Changed Type"

or 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdtPSwJRFEDxryKuHbjz5m9upSCIEGsnLqKCIigw+v5NurCmM4s2nReTF9/Gg3J/u7fdzqMuoipSpGoW7TLKZdnNF/P1x/7+6e79cfa2f3jcDweXq+J2c31zcb4Z3pQRw7OOw189HFw9v74cz4fX4Wn6LkUUq+rrsD99QCwPz3B6+t8ttIiyS/1oiDVV/BpmTR39aIg1TZTtaIg1XRy+lO9DrOmbth8Nc6dSC1MMquP4M/2c/xN0lgN9GKHRhzUafVxj0Yc1Gn1Yo9GHNRp9vFMifRgk0pciA/o4wqKPayz6Jmok+rjGoo9rLPq4xqJvYqc8+jjIpK/MgT6M0OjDGo0+rrHowxqNPqzR6MMajT7eKZE+DDLpSznQhxEafVij0cc1Fn1Yo9GHNRp9WKPRxzsl0odBJn1VDvRhhEYf1mj0cY1FH9Zo9GGNRh/WaPTxTon0YZBJX50DfRih0Yc1Gn1cY9GHNRp9WKPRhzUafbxTIn0YZNLX5EAfRmj0YY1GH9dY9GGNRh/WaPRhjUYf75RIHwaZ9LU50IcRGn1Yo9HHNRZ9WKPRhzUafVij0cc7JdKHQSZ9XQ70YYRGH9Zo9HGNRR/WaPRhjUYf1mj08U6J9GGQSV8Otzk4QqMvq9scEzUWfVnd5uAajb6sbnNM7JRI319uc+w+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Created Date (H)" = _t, #"Original Order Type (L)" = _t, #"Sales Order Pool ID (H)" = _t, #"Sales Order ID (H)" = _t, #"D365 SO Link" = _t, #"Original Legal Entity (L)" = _t, #"Sales Responsible Group ID (H)" = _t, #"Product ID (L)" = _t, SnapDate = _t, #"Ordered Qty (L)" = _t, #"Ordered Qty (L) Before" = _t, #"Ordered Qty (L) New" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Created Date (H)", type datetime}, {"Original Order Type (L)", type text}, {"Sales Order Pool ID (H)", type text}, {"Sales Order ID (H)", type text}, {"D365 SO Link", type text}, {"Original Legal Entity (L)", Int64.Type}, {"Sales Responsible Group ID (H)", type text}, {"Product ID (L)", type text}, {"SnapDate", type text}, {"Ordered Qty (L)", Int64.Type}, {"Ordered Qty (L) Before", Int64.Type}, {"Ordered Qty (L) New", Int64.Type}})
in
    #"Changed Type"

I am trying to calculate the initial sales order status or Ordered Qty(2nd dataset) and final sales order status Ordered Qty(2nd dataset) in a measure based on the dates filters.

For this perticular sales order id, the dates are from 18-03-2023 to 28-03-2023 and the initial status is Open Order so the measure should return Open Order and Invoiced as final status in all rows.

As the date range gets filted and selected from 21-03-2023 to 24-03-2023 then the measures should return as below because the min date sales order status is - Open Order which is Initial Status based on the date range and max date's sales order status is  - Delivered which is final status.

Here i can move the dates from front to back and back to front, but based on the min date and max date for each sales order id and product id's it should give the respective order status.

The above dax is working fine for me. But it is taking too much time and sometimes it doesnt run and throws that resources are exceeded.

Hope i made it clear for you.

I am really looking forward for your right solution for this.

Thanks,

Mohan V.

I have tried below dax which is working fine.

Ordered Qty (L) Before = 
VAR _SalesOrderID = MAX('Sales Orders Snapshots'[Sales Order ID (H)])
VAR _ProductID = MAX('Sales Orders Snapshots'[Product ID (L)])
var _max = MINX(filter(ALLSELECTED('Sales Orders Snapshots'), 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID), 'Sales Orders Snapshots'[SnapDate]) 
return 
CALCULATE(MAX('Sales Orders Snapshots'[Sales Order Status Name]), filter(ALLSELECTED('Sales Orders Snapshots') ,  'Sales Orders Snapshots'[SnapDate] =_max && 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID))  
 

[marcorusso]

The optimization is always a big "it depends".

Using your approach you might use this code:

Ordered Qty (L) Before =
VAR _SalesOrderID =
    MAX ( 'Sales Orders Snapshots'[Sales Order ID (H)] )
VAR _ProductID =
    MAX ( 'Sales Orders Snapshots'[Product ID (L)] )
VAR _max =
    CALCULATE (
        MIN ( 'Sales Orders Snapshots'[SnapDate] ),
        ALLEXCEPT (
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Sales Orders Snapshots'[Ordered Qty (L)] ),
        'Sales Orders Snapshots'[SnapDate] = _max,
        ALLEXCEPT (
            'Sales Orders Snapshots',
            'Sales Orders Snapshots'[Sales Order ID (H)],
            'Sales Orders Snapshots'[Product ID (L)]
        )
    )

The following one might be a better approach if you restrict the selection with the slicers:

Ordered Qty (L) Before =
VAR _FilterSnap =
    CALCULATETABLE (
        ADDCOLUMNS (
            SUMMARIZE (
                'Sales Orders Snapshots',
                'Sales Orders Snapshots'[Sales Order ID (H)],
                'Sales Orders Snapshots'[Product ID (L)]
            ),
            "@SnapDate", MIN ( 'Sales Orders Snapshots'[SnapDate] )
        ),
        ALLSELECTED ()
    )
RETURN
    CALCULATE (
        MAX ( 'Sales Orders Snapshots'[Ordered Qty (L)] ),
        KEEPFILTERS ( _FilterSnap )
    )

However, you are writing a measure that only works on your report and the cost will always be relatively high if you go at the detail level with 9M rows.

[Mohan128256]

@marcorusso thanks for the reply.

But unfortunately the dax you provided is not giving the result what i was getting earlier.

Ordered Qty (L) Before = 
VAR _SalesOrderID =
    MAX ( 'Sales Orders Snapshots'[Sales Order ID (H)] )
VAR _ProductID =
    MAX ( 'Sales Orders Snapshots'[Product ID (L)] )
VAR _max =
    CALCULATE (
        MIN ( 'Sales Orders Snapshots'[SnapDate] ),
        'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID,
        'Sales Orders Snapshots'[Product ID (L)] = _ProductID
    )
RETURN
    // CALCULATE (
    //     MAX ( 'Sales Orders Snapshots'[Ordered Qty (L)] ),
    //     'Sales Orders Snapshots'[SnapDate] = _max,
    //     'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID,
    //     'Sales Orders Snapshots'[Product ID (L)] = _ProductID
    // )
_max

The above _max variable should return the min date for all the records for that sales order id and product id between the dates 18-03-2023 to 28-03-2023
the result is as below

the final output is just results the same value as the ordered qty column. but the result should be the max value of the min date which it should be 40008

Request you to please check the ask above once so that it would be clear for you the output that i am expecting.
here is the sample data that i am working on.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdtPSwJRFEDxryKuHbjz5m9upSCIEGsnLqKCIigw+v5NurCmM4s2nReTF9/Gg3J/u7fdzqMuoipSpGoW7TLKZdnNF/P1x/7+6e79cfa2f3jcDweXq+J2c31zcb4Z3pQRw7OOw189HFw9v74cz4fX4Wn6LkUUq+rrsD99QCwPz3B6+t8ttIiyS/1oiDVV/BpmTR39aIg1TZTtaIg1XRy+lO9DrOmbth8Nc6dSC1MMquP4M/2c/xN0lgN9GKHRhzUafVxj0Yc1Gn1Yo9GHNRp9vFMifRgk0pciA/o4wqKPayz6Jmok+rjGoo9rLPq4xqJvYqc8+jjIpK/MgT6M0OjDGo0+rrHowxqNPqzR6MMajT7eKZE+DDLpSznQhxEafVij0cc1Fn1Yo9GHNRp9WKPRxzsl0odBJn1VDvRhhEYf1mj0cY1FH9Zo9GGNRh/WaPTxTon0YZBJX50DfRih0Yc1Gn1cY9GHNRp9WKPRhzUafbxTIn0YZNLX5EAfRmj0YY1GH9dY9GGNRh/WaPRhjUYf75RIHwaZ9LU50IcRGn1Yo9HHNRZ9WKPRhzUafVij0cc7JdKHQSZ9XQ70YYRGH9Zo9HGNRR/WaPRhjUYf1mj08U6J9GGQSV8Otzk4QqMvq9scEzUWfVnd5uAajb6sbnNM7JRI319uc+w+AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Created Date (H)" = _t, #"Original Order Type (L)" = _t, #"Sales Order Pool ID (H)" = _t, #"Sales Order ID (H)" = _t, #"D365 SO Link" = _t, #"Original Legal Entity (L)" = _t, #"Sales Responsible Group ID (H)" = _t, #"Product ID (L)" = _t, SnapDate = _t, #"Ordered Qty (L)" = _t, #"Ordered Qty (L) Before" = _t, #"Ordered Qty (L) New" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Created Date (H)", type datetime}, {"Original Order Type (L)", type text}, {"Sales Order Pool ID (H)", type text}, {"Sales Order ID (H)", type text}, {"D365 SO Link", type text}, {"Original Legal Entity (L)", Int64.Type}, {"Sales Responsible Group ID (H)", type text}, {"Product ID (L)", type text}, {"SnapDate", type text}, {"Ordered Qty (L)", Int64.Type}, {"Ordered Qty (L) Before", Int64.Type}, {"Ordered Qty (L) New", Int64.Type}})
in
    #"Changed Type"

[marcorusso]

You must keep it simple and not add unnecessary code - filter columns, not tables.

Ordered Qty (L) Before =
VAR _SalesOrderID =
    MAX ( 'Sales Orders Snapshots'[Sales Order ID (H)] )
VAR _ProductID =
    MAX ( 'Sales Orders Snapshots'[Product ID (L)] )
VAR _max =
    CALCULATE (
        MIN ( 'Sales Orders Snapshots'[SnapDate] ),
        'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID,
        'Sales Orders Snapshots'[Product ID (L)] = _ProductID
    )
RETURN
    CALCULATE (
        MAX ( 'Sales Orders Snapshots'[Sales Order Status Name] ),
        'Sales Orders Snapshots'[SnapDate] = _max,
        'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID,
        'Sales Orders Snapshots'[Product ID (L)] = _ProductID
    )

[Mohan128256]

@marcorusso thanks for the quick response.

I have modified the dax as you suggested.

Ordered Qty (L) Before = 
VAR _SalesOrderID =
    MAX ( 'Sales Orders Snapshots'[Sales Order ID (H)] )
VAR _ProductID =
    MAX ( 'Sales Orders Snapshots'[Product ID (L)] )
VAR _max =
    CALCULATE (
        MIN ( 'Sales Orders Snapshots'[SnapDate] ),
        'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID,
        'Sales Orders Snapshots'[Product ID (L)] = _ProductID
    )
RETURN
    CALCULATE (
        MAX ( 'Sales Orders Snapshots'[Ordered Qty (L)] ),
        'Sales Orders Snapshots'[SnapDate] = _max,
        'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID,
        'Sales Orders Snapshots'[Product ID (L)] = _ProductID
    )

But this is been running from 10min and no result yet.

As i mentioned before, the data size of this table is around 9,64,000 records.

Please help.

[marcorusso]

Filter columns, not tables: Filter Arguments in CALCULATE - SQLBI

[Mohan128256]

@marcorusso thanks for the reply.

I did change the measure varibale _max to below dax

var _max = MINX((filter(ALLSELECTED('Sales Orders Snapshots'[Sales Order ID (H)],'Sales Orders Snapshots'[SnapDate],'Sales Orders Snapshots'[Product ID (L)]), 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID)), 'Sales Orders Snapshots'[SnapDate]) 

If i change to ALL function as per your blog, it is not giving the right result, so i changed it to allselected and it is working.

But the perfornamce of this single variable is taking around 18 seconds for one single selected customer.

and for below dax it took around 23 sec for one single customer selected sales order id's

Ordered Qty (L) Before = 
VAR _SalesOrderID = MAX('Sales Orders Snapshots'[Sales Order ID (H)])
VAR _ProductID = MAX('Sales Orders Snapshots'[Product ID (L)])
var _max = MINX((filter(ALLSELECTED('Sales Orders Snapshots'[Sales Order ID (H)],'Sales Orders Snapshots'[SnapDate],'Sales Orders Snapshots'[Product ID (L)]), 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID)), 'Sales Orders Snapshots'[SnapDate]) 
return 
CALCULATE(MAX('Sales Orders Snapshots'[Ordered Qty (L)]), filter(ALLSELECTED('Sales Orders Snapshots'[Sales Order ID (H)],'Sales Orders Snapshots'[SnapDate],'Sales Orders Snapshots'[Product ID (L)]) ,  'Sales Orders Snapshots'[SnapDate] =_max && 'Sales Orders Snapshots'[Sales Order ID (H)] = _SalesOrderID && 'Sales Orders Snapshots'[Product ID (L)] = _ProductID))  

is ther anything which i modifiy in the dax

[Mohan128256]

@marcorusso @AlbertoFerrari 

Request you to please have a look at this ask and help to optimize the dax query.