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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
738o51
Helper II
Helper II

Moving average measure with slow performance

Hi community,

 

I have built a moving average measure but it is having some performance issues (takes 1-2 minutes to load or to adjust when I change filters). I've already edited my data model to make it as efficient as possible. It seems like the rest of the report is working smoothly, but this measure is being particularly slow. Can anyone suggest a more efficient way to build it?

 

My measure is trying to estimate the number of customers who are returning to a retail location within 30 days since their last visit and who are purchasing something.

 

Here is the rolling 7 average that is having performance issues:

Rolling 7 day unique return customers making purchase = 
var sumreturnpurchaseslast7 = 
CALCULATE(
    [Unique return customers making purchase],
    DATESINPERIOD(
        'Date Dimension'[Date],
        LASTDATE('Date Dimension'[Date]),
        -7,
        DAY))
RETURN
divide(sumreturnpurchaseslast7,7)

Here is the measure it is based on:

Unique return customers making purchase:
CALCULATE(
    DISTINCTCOUNT('Customer visit fact'[ID]),
    'Customer visit fact'[Purchase status]="Purchase made",
    'Customer visit fact'[Visit is 30 days or less from previous]=1)

 The date dimension table has a one to many relationship with the purchase date field in the Customer Visit fact table.

 

Any help would be greatly appreciated!

 

Thanks,

738051

3 REPLIES 3
v-luwang-msft
Community Support
Community Support

Hi @738o51 ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

v-luwang-msft
Community Support
Community Support

Hi @738o51 ,

So far, It don't see any problems with your Dax (it seems to me that it can no longer be optimized), is it that the amount of data in the table is so large that it takes longer to calculate the rolling 7-day calculation?

 

Best Regards

Lucien

AlexisOlson
Super User
Super User

You could try SUMX ( DISTINCT ( 'Customer visit fact'[ID] ), 1 ) instead of DISTINCTCOUNT('Customer visit fact'[ID]) as discussed here. Might not help at all but it's worth a try.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.