Forum Discussion

Akos07's avatar
Akos07
Helper I
3 years ago
Solved

Optimalize dax

Hello,

 

I would like to ask for some help.

 

I have 2 view from sql server. The first view collects all the delivery documents (product, date, quantity, store, price), and the other collects all the sales with the same columns.

 

Delivery view has 2,5 million record, Sales view has 18 million record.

 

For each  sales row, i have to find the closest delivery price.  I created a new colum in the sales view with the following formula

 

CurrentDeliveryPriceGross =

    MAXX (
        TOPN (
            1,
            FILTER (
                view_PBI_BTK_Delivery ,
                view_PBI_BTK_Delivery [storeid] = view_PBI_BTK_Sales[storeid]
                && view_PBI_BTK_Delivery [CikkCsomEgysegId] = view_PBI_BTK_Sales[CikkCsomEgysegId]
                && view_PBI_BTK_Delivery [date] <= view_PBI_BTK_Sales[date]
            ),
            view_PBI_BTK_Delivery [date], DESC,view_PBI_BTK_Delivery [Id],DESC
        ),
        view_PBI_BTK_Delivery [price]
    )
 
The idea is to find for each sales record the correct last delivery price. Each store has different delivery price and/or date, so i have to search in the store, the product, and the date. The 2 view is nor connected, it would be a manny to manny connection.
 
If i have ~5 million sales record the BI can calculate this, and i got the correct price, but i need this information for 18 or more record. Each time with this amount of record i hit the refresh, i get memory problem. 
 
Can somebody suggest a method or new formula for this problem?
 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    HI Akos07,

    I think they should be more related to your data amounts. The iterate calculation through two table with huge amount of records may cause the performance issues. (the total calculation amount= TableA row count * TabeB row count)

    In addition, you can also try to use the following formulas if they help:

    measure version:

     

    CurrentDeliveryPriceGross =
VAR currdate =
    MAX ( view_PBI_BTK_Sales[date] )
RETURN
    CALCULATE (
        MAX ( view_PBI_BTK_Delivery[price] ),
        FILTER (
            ALLSELECTED ( view_PBI_BTK_Delivery ),
            [date] <= currdate
        ),
        VALUES ( view_PBI_BTK_Sales[storeid] ),
        VALUES ( view_PBI_BTK_Sales[CikkCsomEgysegId] )
    )

     

    Calculate column version:

     

    CurrentDeliveryPriceGross =
CALCULATE (
    MAX ( view_PBI_BTK_Delivery[price] ),
    FILTER (
        view_PBI_BTK_Delivery,
        [storeid] = EARLIER ( view_PBI_BTK_Sales[storeid] )
            && CikkCsomEgysegId = EARLIER ( view_PBI_BTK_Sales[CikkCsomEgysegId] )
            && [date] <= EARLIER ( view_PBI_BTK_Sales[date] )
    )
)

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    HI Akos07,

    I think they should be more related to your data amounts. The iterate calculation through two table with huge amount of records may cause the performance issues. (the total calculation amount= TableA row count * TabeB row count)

    In addition, you can also try to use the following formulas if they help:

    measure version:

     

    CurrentDeliveryPriceGross =
VAR currdate =
    MAX ( view_PBI_BTK_Sales[date] )
RETURN
    CALCULATE (
        MAX ( view_PBI_BTK_Delivery[price] ),
        FILTER (
            ALLSELECTED ( view_PBI_BTK_Delivery ),
            [date] <= currdate
        ),
        VALUES ( view_PBI_BTK_Sales[storeid] ),
        VALUES ( view_PBI_BTK_Sales[CikkCsomEgysegId] )
    )

     

    Calculate column version:

     

    CurrentDeliveryPriceGross =
CALCULATE (
    MAX ( view_PBI_BTK_Delivery[price] ),
    FILTER (
        view_PBI_BTK_Delivery,
        [storeid] = EARLIER ( view_PBI_BTK_Sales[storeid] )
            && CikkCsomEgysegId = EARLIER ( view_PBI_BTK_Sales[CikkCsomEgysegId] )
            && [date] <= EARLIER ( view_PBI_BTK_Sales[date] )
    )
)

     

    Regards,

    Xiaoxin Sheng

  • Akos07's avatar
    Akos07
    Helper I
    3 years ago

    Thank you, i found a workround, i added the column in the a view, BI just import the data, and now its working.