Poor performance of GROUPBY vs SUMMARIZE

[Anonymous]

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] )
)

[fcarvalho]

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] )

[amitchandak · Accepted solution]

@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/

[Anonymous]

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. 

[Greg_Deckler]

@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.