Measure taking forever to execute

[danielgergely]

Hello everyone

I have a reacurring issue

I have a measure which calculates the starting id (row number) and the ending id according to some conditions. These conditions change for every row. After having the starting and ending ID, I do some calculations and expect a result. The only problem is that the measure takes forever to execute. 

Is there a way to optimise the measure for faster execution time?

The table is a bill of materials which all the components are listed hierarchically. (Component on level 0 contains levels 1s and 1s the 2s all the way up to 10 levels see example below:)

0

 .1

  ..2

   ...3

  ..2

 .1

  ..2

...

This is my first measure:

GP_M = 
VAR _id = SELECTEDVALUE(Table[id]) // row number of current row
VAR _level = SELECTEDVALUE(Table[Level#])  // level 0-10
VAR _nextId = CALCULATE(FIRSTNONBLANK(Table[id], Table[id]), FILTER(ALL(Table), Table[id] > _id && Table[Level#] <= _level))  // next row number where the level is the same
VAR _minLevel = CALCULATE(MIN(Table[Level#]), FILTER(ALL(Table), Table[id] > _id && Table[id] < _nextId))  // smallest level in the section
VAR _type = SELECTEDVALUE(Table[Type])  // type "E" or "F"
VAR _condition1 = IF(_minLevel = BLANK() && _type = "E", MAX(Table[Price]), IF(_minLevel = BLANK() && _type = "F", 0, [GP_M2]))  // if the conditions are true give me the price, if not calculate some more
RETURN _condition1

This is my second measure:

GP_M2 =
VAR _id = SELECTEDVALUE(Table[id])
VAR _level = SELECTEDVALUE(Table[Level#])
VAR _level1 = _level +1  // the next level
VAR _nextId = CALCULATE(FIRSTNONBLANK(Table[id], Table[id]), FILTER(ALL(Table), Table[id] > _id && Table[Level#] <= _level))
VAR _minLevel = CALCULATE(MIN(Table[Level#]), FILTER(ALL(Table), Table[id] > _id && Table[id] < _nextId))
VAR _type = SELECTEDVALUE(Table[Beschaffungsart])
VAR _condition1 = IF(_minLevel = BLANK() && _type = "E", MAX(Table[Price]), IF(_minLevel = BLANK() && _type = "F", 0, BLANK()))  // if the conditions are true, give me the price. If not return blank (I would create a new calculation instead of blank)
RETURN  _condition1

I need to calculate all 10 (optional) levels the same way. (Using one measure to calculate and reusing some variables also only slightly increases the speed)

Any suggestions, tipps? 
(The first measure executed after 15+ minutes of "working")

Thanks
Daniel

[Magpie_Rob]

you could try changing the filters to calculatetable - Curbal has done a video about it here . 
DAX Fridays Battle #188: FILTER vs CALCULATETABLE - Avoid using FILTER as a filter argument - YouTub...

Also try and compute the tables before performing the DAX sometime when I create summarizecolumn tables then perform the dax ontop it turns out being quicker! good luck!

[danielgergely]

@Magpie_Rob 
Thanks for your reply
I watched the video, and changed my measure to use CALCULATETABLE() instead of FILTER().
Its greate to know about this itterative functionality of FILTER(), but in my case, it didn't help. 
I have to itterate through every row, and then say where the next ID is according to two conditions. 
This is my simplified measure. I backed up and started with only calculating the next ID in a measure, which then I would use in my further calculations.

Before:

Next_id = 
VAR _id = SELECTEDVALUE(Table[id])
VAR _level = SELECTEDVALUE(Table[Level#])
VAR _nextId = CALCULATE(MIN(Table[id]), FILTER(ALL(Table), Table[id] > _id && Table[Level#] <= _level))
RETURN _nextId

After:

Next_id = 
VAR _id = SELECTEDVALUE(Table[id])
VAR _level = SELECTEDVALUE(Table[Level#])
VAR _nextId = CALCULATE(MIN(Table[id]), CALCULATETABLE(Table, Table[id] > _id,  Table[Level#] <= _level))
RETURN _nextId

[lbendlin]

Try to use MIN(), add an index (key) column, see how many blanks you actually have in that column etc.  Don't despair!

[danielgergely]

@lbendlin Thanks for your reply

I already have an index column. (The ID column)

I tried inestead of FIRSTNONEBLANK() to use MIN(). It maybe has a slight impact, but its not significant. The 2 measures still take 10+ Minutes to execute. (Sometimes I even get an out of memory error).

[lbendlin]

Install DAX Studio and use it to display the query execution plan. Based on the output you can decide where to optimize your measure. Most likely the FIRSTNONBLANK() is the culprit, but DAX Studio will tell you exactly.

[danielgergely]

I know why it takes so long. It is the _nextId variable.
It is because I preform an expensive calculation for every row. The table in which I have to search for values has a little less then 500 000 rows... I don't know if there is a better method to do this