Measure to get historic category averages is much too slow

[yiker]

Hi all,

I'm new here, but have a persistent issue with one of my measures that really slows down my report. Hoping someone can help me out.

Context
We organize workshops that are then being rated by the workshop participants.

Data Model (simplified)
I have three tables

1. training_ratings - Fact table. Contains all the ratings submitted by participants and a classroom_id
2. classrooms - Dimension table. Contains information on the workshop itself, such as the name and topic of the workshop as well as the client that commissioned the workshop
3. classroom_events - Dimension table. Simply a list of each classroom_id and the date on which it took place.

classrooms filters training_ratings through a one-to-many relationship via classroom_id

classrooms and classroom_events filter each other through a one-to-one relationship via classroom_id
yes I could simply merge classroom_events with classrooms, but to my knowledge that wouldn't make any difference

The measure

I'm setting up a table of the average rating for each classroom. in this table I also want to display the average rating across all classrooms with the same topic that have been given in the past.

I wrote a measure looking like this:

Historic Topic Rating = 
var topics = values(classrooms[topic])
var latest_classroom = CALCULATE(max(classroom_events[date]))

return
CALCULATE(Average(training_ratings[rating])
    , ALL(training_ratings)
    , classrooms[topic] in topics
    , classroom_events[date] < latest_classroom
)

This seems to work but when put in a table causes it to load forever.

Are there any different ways of approaching this problem?

Any help is much appreciated!

[v-angzheng-msft · Accepted solution]

Hi, @yiker 

This measure does not seem to be complicated. Does the file contain too much data?
Changing the data connection mode, changing the data structure, and improving the hardware performance of the machine can all be considered.

You can use tools such as performance analyzer to analyze the performance of the query.
You can refer to the following:

DAX Best Practice Guide
Optimization guide for Power BI

Use Performance Analyzer to examine report element performance

Analyzing a slow report query in DAX Studio

Boost the performance of Power BI with these analyzer tools

Best Regards,
Community Support Team _ Zeon Zheng

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