DAX Out of memory error in a measure

[Anonymous]

Greetings, I am getting a Out of memory error in a measure. I have over 16 GB of ram. The measure is below 

Average Manage Score = aVERAGEX( aLL(Education_Metrics),  [Tiered Manage Education Score])

Tiered Manage Education Score = ([Tiered Manage Courses Weight] +[Manage Certification Weightage]  +[Manage Life Cycle Weightage 2] )/[Manage Seats Weightage 3]

Any tips what could cause this issue to go away?

[Anonymous]

There's nothing wrong about averaging a measure. The problem you most likoly have is that you want to average it over a large set of rows and since a measure always performs context trastition (and this is an expensive operation) you'll almost surely get an error and in addition the calculation will be slow. Often agonizingly slow. On top of that, your measure is composed of more measures that might be doing something slow or might require a lot of materialization (because the measures are not optimized, for instance).

[Anonymous]

@Anonymous In my view i have optimised all possible ways where possible and created calculated coluns at a couple of places. But the averagex( ) still gives an out of memory problem. It works when there is a filter on a single value but not for all (which is what i want and average of all). How else can i do an average on a measure without going through each row. As i am interested in averaging all the values of the measure and not each row of the measure. This seems to be a limitation in power bi?

[Anonymous]

Okay Thank you, let me try to optimise it and see if that works.

[Anonymous]

Golden rule of DAX: You should never call a measure in a row context on a big fact table. This will almost always result in an out-of-mem error. Maybe you should think about structuring your model in a better way to change the DAX. Averaging measures over small dimensions or small tables is OK but not over big tables.

[Anonymous]

Thanks for reverting, So may i ask what is an alternative for averaging a Measure. If its not at a row level it may be alright. But to my newly gained knowledge about DAX, i was able to find only Averagex that could average a measure, there is no other way of averaging a measure that i was able to find.

[Anonymous]

@Anonymous 

Fact tables are not there to run measures against them. Dimensions are created exactly for this reason. Fact tables hold figures that measure your process and then you build measures/aggregations on top of these figures. Slicing and dicing is performed only through dimensions. Fact tables, in fact, should always be hidden from view. You should never iterate them row by row invoking measures in the meantime. If you have to do something like this, it means your design is most likely flawed. Please stick to the star schema and you should be safe.

By the way, AVERAGEX can be also calculated differently since an arithmetical average is just a sum of values divided by the number of values. So, broadly speaking, average = sum / count.

[AntrikshSharma]

@Anonymous  What are the total number of rows of Education_Metrics? You have 4 measures in a row context, depending on what they calculate the calculation could be pretty intense. The issue here should be that your dimension/fact table is huge and context transition is taking up too much time.

[Anonymous]

@AntrikshSharma there are about  120,000 rows in that table.
the measure without averagex is okay. Afer adding averagex it gives the error. Is there an alternate to average for a measure ?

[AntrikshSharma]

@Anonymous  Okay, so the problem is when you call a measure in the row context, context transition happens which means every row of the table is converted into a an equivalent filter context under which the measure used in the row context will be evaluated, and here it is being done 120K times, that's what is causing the issue.

Try to include only those columns in ALL which you are using/slicing by in the report and then share your findings.