AverageX and Summarize Measure with Slow Performance

[SerenaFan93]

Hello,

I have a measure below that is taking 1-2 minutes to load and slowing down all of my associated visuals.

I'm trying to capture the average number of subsequent visits customers have at other stores, excluding their initial visit. The AllShopingEvents table has all events the customer had and EventType differentiates the visits from the initial visit and subsequent visits.

Average Number of Additional Events = AVERAGEX(SUMMARIZE(AllShoppingEvents,AllShoppingEvents[Customer_ID],"Event Count",(CALCULATE(COUNT(AllShoppingEvents[ShopType]),AllShoppingEvents[EventType]="Subsequent Event"))),[Event Count])

Any help making the measure run faster would be appreciated. Thank you!

[v-rongtiep-msft]

Hi @SerenaFan93 ,

Please have a try.

Average Number of Additional Events = 
VAR _table1 =
    CALCULATETABLE (
        COUNT ( AllShoppingEvents[ShopType] ),
        AllShoppingEvents[EventType] = "Subsequent Event"
    )
VAR _table2 =
    SUMMARIZE (
        AllShoppingEvents,
        AllShoppingEvents[Customer_ID],
        "Event Count", _table1
    )
RETURN
    AVERAGEX ( _table2, [event count] )

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without  priavcy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SerenaFan93]

Hi Polly,

Thank you for your help. When I run the code above, I get an error that the COUNT is not the correct parameter type, as it expecting a table name. When I update it to the table name (code below), I return the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value".

Average Number of Additional Events =

VAR _table1 = CALCULATETABLE (

AllShoppingEvents,

AllShoppingEvents[EventType] = "Subsequent Event"

)

VAR _table2 = SUMMARIZE (

AllShoppingEvents,

AllShoppingEvents[Customer_ID],

"Event Count",

_table1

)

RETURN

AVERAGEX ( _table2, [event count] )

The end goal is to have the average number of a subsequent visits, if the customer uses another shop type.

[v-rongtiep-msft]

Hi @SerenaFan93 ,

Please try this.

Average Number of Additional Events =
VAR _table1 =
    SUMMARIZE (
        AllShoppingEvents,
        AllShoppingEvents[Customer_ID],
        "Event Count",
            (
                CALCULATE (
                    COUNT ( AllShoppingEvents[ShopType] ),
                    AllShoppingEvents[EventType] = "Subsequent Event"
                )
            )
    )
RETURN
    AVERAGEX ( _table1, [event count] )

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SerenaFan93]

Hi Polly,

This code worked, thank you! However, the matrices and other visuals are still running very slowly. I wonder if SUMMARIZE is slowing down the measures.

[v-rongtiep-msft · Accepted solution]

Hi @SerenaFan93 ,

First, make sure your data model is set up correctly. DAX is set up to work in a specific manner so you have to go with its flow to have a smoother experience.

Next, find the measures that are causing the most problems. Run them in the Server Timings in DAX Studio and look for unnecessary rows and Call Back Data IDs which affect the total duration of the measure’s calculation.

You can also find them in the Records column in the Physical Query Plan. Make sure that the number of records is equal or close to the number of rows of the final output. This ensures that your query is materializing enough tables for the results.

DAX Query Optimization Techniques And Lessons - Enterprise DNA

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.