topic Re: DAX Out of memory error in a measure in DAX Commands and Tips

DAX Out of memory error in a measure

Anonymous — Tue, 03 Nov 2020 18:27:32 GMT

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?

Re: DAX Out of memory error in a measure

AntrikshSharma — Tue, 03 Nov 2020 18:32:01 GMT

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.

Re: DAX Out of memory error in a measure

Anonymous — Tue, 03 Nov 2020 18:38:55 GMT

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 ?

Re: DAX Out of memory error in a measure

AntrikshSharma — Tue, 03 Nov 2020 18:44:15 GMT

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.

Re: DAX Out of memory error in a measure

Anonymous — Wed, 04 Nov 2020 14:59:33 GMT

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.

Re: DAX Out of memory error in a measure

Anonymous — Wed, 04 Nov 2020 15:03:16 GMT

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.

Re: DAX Out of memory error in a measure

Anonymous — Wed, 04 Nov 2020 15:33:14 GMT

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).

Re: DAX Out of memory error in a measure

Anonymous — Thu, 05 Nov 2020 15:18:34 GMT

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

Re: DAX Out of memory error in a measure

Anonymous — Tue, 10 Nov 2020 21:55:34 GMT

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?

Re: DAX Out of memory error in a measure

Anonymous — Wed, 11 Nov 2020 12:44:44 GMT

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.