Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
)
Solved! Go to 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/
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] )
@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/
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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |