cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Memory issues with DAX filter

Hi,

 

I have a model where I have a Customers table and an Orders Table. I want to add a new mesure in the Customers table where i want to compute the average buying cyle. So i add this DAX mesure:

 

 

    BUYING_CYCLE = 
            AVERAGEX(ORDERS; 
                    DATEDIFF( CALCULATE
                        MAX ( 'ORDERS'[ORDER_DATE] ) ;
                            FILTER(ORDERS; EARLIER('ORDERS'[ORDER_DATE]) >= 'ORDERS'[ORDER_DATE] )
                    ); 'ORDERS'[ORDER_DATE]
                    ; DAY )
            ) 

 

This works fine but it's not correct. I should add a second filter like this:

 

FILTER(ORDERS; EARLIER('ORDERS'[ORDER_DATE]) >= 'ORDERS'[ORDER_DATE] ) && EARLIER('ORDERS'[ID]) <> 'ORDERS'[ID] )

 

But if I add this second expression the query is really slow and the memory gets to 98 plus %.  There aren't a lot of records in the Orders table, like 25000. 

 

Can anybody tell me how can i understand what is happening here?

 

Thank you.

2 REPLIES 2
Highlighted
Advocate III
Advocate III

Re: Memory issues with DAX filter

I think you can do the filter without the earlier() function.

 

try: FILTER(ORDERS; VALUES('ORDERS'[ORDER_DATE]) >= 'ORDERS'[ORDER_DATE] ) && VALUES('ORDERS'[ID]) <> 'ORDERS'[ID] )

 

as you cycle through all of your orders, you could create a calculated column in your orders table to get the 'previous order date'.  You would have to add a condition for 'the same customer':

 

CALCULATE ( 
                        MAX ( 'ORDERS'[ORDER_DATE] ) ;
                             FILTER(ORDERS; EARLIER('ORDERS'[ORDER_DATE]) >= 'ORDERS'[ORDER_DATE] ) && EARLIER('ORDERS'[ID]) <> 'ORDERS'[ID] && EARLIER('CUSTOMER'[ID]) = 'CUSTOMER'[ID])
                     )

 

Regards,

Dries

Highlighted
Resident Rockstar
Resident Rockstar

Re: Memory issues with DAX filter

My understanding from your requierment .

 

U have customer table, Order Table.

 

U want to know the Customer average ? am right ?

 

if yes ? do u have relarionship between this two tables right ?

 

then u can use direct average function . i know i misunderstood.

 

Can u please share your sample data, and your expected output i will help u.

 

I think u want to know the Customer Running Average.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors