Forum Discussion
AverageX and Summarize Measure with Slow Performance
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.
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.
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.
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.
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.
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.