Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors