Very slow Dax query - based on "between"-slicer - how optimize?

[1up]

Hi,

My measure is very low, one single line takes 2 seconds to complete. How can this be optimized? I realize it may be a combination of the relationship model, dax query etc.

I thought I would try with 1) look for help to optimize the dax query itself if possible and 2) adjust the relationship model so that a many-many-relation is added with a bridge table. But not sure the performance gain this may give.

So, to the Dax query itself, it is filtering a big table based on the user's min and max selections, to return the rows / values between this min and max value.

How would you have written the measure? Is it OK or can it be optimized?

Filtered_Measure =

VAR MinV = MIN('Table[Column])

VAR MaxV = MAX('Table[Column])

RETURN

                            CALCULATE(

                                    [Measure],  

                                        'Table'[Column] >= MinV &&

                                        'Table'[Column] <= MaxV

                                    )

Perhaps there are faster ways to "pick" the selected value of a slicer for the min and max value, I fear the current formula is looking through the actual filtered complete table looking for the min and max values, instead of just picking the ones the user has already specified, but not 100% sure.

All help is welcome,

Daniel

[1up · Accepted solution]

I tried creating a parameter which is a table with much fewer rows than the original one. And using it as a between slicer, which then by a measure affects the results.

That was probably part of the solution. I also replaced a many-to-many-relation with a bridge table which may have sped things up more.

A direct way to pick both two separate values in a between slicer I haven't found as of yet.

[1up]

I red through different Dax-functions like values(), thanks. I didn't find one that suited my exact needs to pick efficiently the two separate selected values in a between-slicer. May still be one, but haven't been able to identify one in that case.

[1up · Accepted solution]

Creating a parameter with a small table (few rows) compared to the actual database table sped things up. Also created a bridge table, replacing a many-to-many relationship.

[1up · Accepted solution]

I tried creating a parameter which is a table with much fewer rows than the original one. And using it as a between slicer, which then by a measure affects the results.

That was probably part of the solution. I also replaced a many-to-many-relation with a bridge table which may have sped things up more.

A direct way to pick both two separate values in a between slicer I haven't found as of yet.

[lbendlin]

As posted your CALCULATE filter does nothing. Side note - instead of && use commas.

You will want to evaluate the actual [Measure]  code.

[1up]

Hi,

Not sure I understand fully.

The measure appears to work OK. I have thought that when not using the FILTER-statement, it is inferred from the context.

Is using ","-sign instead of && viable in this case? I need both conditions fulfilled for the measure's results.

A follow-up question is what is the most effective way / fastest dax to get the value specified in slicers by the users, like in a between slicer, the minimum value, and the maximum value, those two ones. I have looked at selectedvalue() but didn't get it to work here if I remember correctly.

Sorry if I have made some slight wrong-writing of the measure above, in transitioning it from my actual measure to this simpler one,.

[lbendlin]

Read about FILTERS and VALUES and MAXX amd MINX.