Optimalize dax

[Akos07]

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?

[Akos07]

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

[v-shex-msft · Accepted solution]

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