DAX table Optimisation and calculation groups

DAX DAX Showcase
1

Hi guys
I’m in the process of optimising a DAX table built to provide a feed to a paginated report. All working but table takes over a minute to build (and sometimes causes memory issues on refresh).

3 questions
I’ve read that KEEPFILTERS can be quicker than FILTER, but I cannot think thru the equivalent KEEPFILTERS syntax for FILTER(Reports,ISBLANK(Reports[PagRepGroup002])=False).

The table is running 12 measures, 11 of which could be set up as a calculation group. So question is does a calculation group of 11 items run more quickly than 11 separate measures?

Then if calculation groups are more optimal, how do you get the group into a SUMMARIZECOLUMNS?

Third question is what is the best tool for looking for the bottlenecks. I do have DAX studio and Tab Editor 2, but could someone point me to a good tutorial on TE2 for checking optimisation?

Thanks
Pete

2

@BINavPete

You can use KEEPFILTERS like this -
CALCULATE ( [Measure], KEEPFILTERS ( Table[Column] = “Something” ) )

It depends 11 Separate measures could be more efficient if written in the right way, Calculation Groups are not higly optimized at the moment. But there is a caveat, IF and SWITCH can use either Strict or Eager evaluation method to evaluate the measures so without looking at the setup I can’t confirm.

DAX Studio is the best tool for identifying the bottleneck and I created 2 courses for EDNA in January that help newbies in getting started with DAX Studio and DAX optimizations.

Some other resources (not all are for optimization):

4 Likes
3

Thanks @AntrikshSharma
Plenty of useful reading there. Might do some of that on hols.

I seem to have fixed a good lump of my speed problem with a hard look at the ROLLUPADISSUBTOTAL piece of my code and reducing 4 Rollups into 2. So now code is running in 40s rather than 80s which is cool.

I’ll take at look at some of your stuff in a couple of weeks when back from hols because I think there is more I can do here.
Going back to KEEPFILTERS it is the blank bit that is tricky. So this works
FILTER(Reports,ISBLANK(Reports[PagRepGroup002])=False)
but this KEEPFILTERS(Reports[PagRepGroup002]<>Blank()) gives aggregation error and this KEEPFILTERS(max(Reports[PagRepGroup002])<>Blank()).
This is all happening within a SUMMARIZECOLUMNS pattern.

Take care and thank you for resources
Pete

1 Like
4

@BINavPete
Yup reducing sub-totals and grand totals is a good idea as some times for them the engine does a separate scan of the data and sometime it doesn’t.

Are you using KEEPFILTERS directly inside SUMMARIZECOLUMNS? Because that is not allowed, KEEPFILTERS is a Calculate Filter Modifier + can been used with an iterator.

CALCULATE ( [Total Sales], KEEPFILTERS ( Products[Color] = "Red' ) )
or
FILTER ( KEEPFILTER ( ALL ( Products ) ), [Total Sales] )

but not

SUMMARIZECOLUMNS ( 
    Products[Color],
    KEEPFILTERS ( Products[Color] = "Red" ),
    "Sales Amount", [Total Sales]
)
5

Hi @BINavPete, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.