PowerPivot DAX MAXX Peformance Issue

Question

I am building a data model with PowerPivot for Excel 2013 and need to be able to identify the max number of emails sent per person. The DAX formula below gives me the result that I looking for but performance is incredibly slow. Is there an alternative that will compute a maximum by group without the performance hit?

Maximum Emails per Constituent:

=MAXX(SUMMARIZE('Email Data','Email Data'[person_id],"MAX Value",
    ([Emails Sent]/[Unique Count People])),[MAX Value])

greggyb greggyb · Accepted Answer · 2015-10-26T22:41:37

So, without the measure definitions for [Emails Sent] or [Unique Count People], it is not possible to give definitive advice on performance. I'm going to assume they are trivial measures, though, based on their names - note that this is an assumption and its truth will affect the rest of my post. That being said, there is an obvious optimization to make to start with.

Maximum Emails per Consultant:=
MAXX(
    ADDCOLUMNS(
        VALUES('Email Data'[person_id])
        ,"MAX Value"
        ,[Emails Sent] / [Unique Count People]
    )
    ,[MAX Value]
)

I used the ADDCOLUMNS() rather than a SUMMARIZE() to calculate new columns. See this post for an explanation of the performance implications.

Additionally, since you're not grouping by multiple columns, there's no need to use SUMMARIZE(). The performance impact of using VALUES() instead should be minimal.

The other question that comes to mind is whether this needs to be a measure. Are you going to be slicing by other dimensions? If not, this becomes a static attribute of a [person_id] which could be calculated during ETL, or in a calculated column.

A final note - I've also been assuming that your model is optimal as well. Again, we'd need to see it to make comment on whether you could see performance issues from something you're doing there.

PowerPivot DAX MAXX Peformance Issue

1 Answers