cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Akos07
Helper I
Helper I

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?
 
1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
Akos07
Helper I
Helper I

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
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.