topic Re: Poor performance of GROUPBY vs SUMMARIZE in DAX Commands and Tips

Poor performance of GROUPBY vs SUMMARIZE

Anonymous — Wed, 02 Sep 2020 01:44:16 GMT

Hi DAX experts,

From all I've read, GROUPBY() should be at least as performant as SUMMARIZE() but I've found it substantially slower. Below are two equivalent queries - the GROUPBY() version takes about 6x longer. Any advice on why there is such a big difference?

Thanks!

GROUPBY():
CALCULATETABLE (
    GROUPBY (
        Fact_PlexosSolutionData,
        Dim_Date[FinancialYear],
        Dim_Model[ModelId],
        "TotalGenCost", SUMX ( CURRENTGROUP (), Fact_PlexosSolutionData[Generators.GenerationCost.$] )
    ),
    FILTER ( Dim_Facility, Dim_Facility[IsPPA] )
)

SUMMARIZE():
CALCULATETABLE (
    SUMMARIZE (
        Fact_PlexosSolutionData,
        Dim_Date[FinancialYear],
        Dim_Model[ModelId],
        "TotalGenCost", SUM ( Fact_PlexosSolutionData[Generators.GenerationCost.$] )
    ),
    FILTER ( Dim_Facility, Dim_Facility[IsPPA] )
)

Re: Poor performance of GROUPBY vs SUMMARIZE

Greg_Deckler — Wed, 02 Sep 2020 01:53:07 GMT

Anonymous - This does not surprise me, GROUPBY must use an interator function like SUMX, etc. so that's likely the source of the performance difference.

Re: Poor performance of GROUPBY vs SUMMARIZE

amitchandak — Wed, 02 Sep 2020 02:37:51 GMT

Anonymous , while groupby can slow down the performance because it needs sumx(X functions) . It has its own advantages. So those case you should use group by. But you can avoid it where something else can be used.

https://www.sqlbi.com/blog/marco/2016/02/15/groupby-vs-summarize-in-dax-powerbi-powerpivot/

https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/

Re: Poor performance of GROUPBY vs SUMMARIZE

Anonymous — Wed, 02 Sep 2020 03:17:49 GMT

Thanks amitchandak and Greg_Deckler for your insights. It does seem to be the SUMX that slows it down. Removing the aggregation gives roughly equal performance.

Re: Poor performance of GROUPBY vs SUMMARIZE

fcarvalho — Mon, 05 Apr 2021 17:31:54 GMT

Hi,

There is any alternative to AverageX and Summarize?

In terms of performance I can't load a table properly. Always getting that the visual exceeded the resources..

For example:

Measure = AVERAGEX( SUMMARIZE( VALUES( Client[ClientID] ) , "average", [Discount] ), [average] )