Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Inconsistent results with SUMMARIZE

I extracted this issue from an 2016 post, as the "recent" update to the SUMMARIZE behavior did not fix the issue described there (
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Is-there-sth-wrong-with-summarize-function/m-...).


Let us create two queries Sales and Sales2 with the queries below. Note that Sales2 references Sales and only differs in the column ordering.

 

// Sales
let
Source = Table.FromRows(Json.Document(Binary.Decompress
(Binary.FromText("i45WcsrMTlXSUQpKTQGSTkqxOtFKwRmZRSU4xNyLUlPz4KJQzTBBR8JKnXJKU+EqMcRiAQ==",
BinaryEncoding.Base64),Compression.Deflate)),
let _t = ((type text) meta [Serialized.Text = true])
in type table [Product = _t, Color = _t, Model = _t]),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Product", type text},
{"Color", type text}, {"Model", type text}})
in
#"Type modifié"
// Sales2
let
Source = Sales,
#"Colonnes triées" = Table.ReorderColumns(
Source,{"Model", "Product", "Color"})
in #"Colonnes triées"



Now, let us create two calculated tables in our model.

 

TestSales = SUMMARIZE ( Sales;
    Sales[Color];
    "AllColorSales"; 
    CALCULATE (
        COUNTROWS ( Sales ); 
        ALL ( Sales[Color] )
     )
)

 

And the equivalent query based on Sales2:

TestSales2 = SUMMARIZE ( Sales2;
     Sales2[Color];
       "AllColorSales2"; 
       CALCULATE ( 
            COUNTROWS ( Sales2 )
                ; ALL ( Sales2[Color] ) 
            )     
    )


Unexpectedly, both queries do not return the same results:


TestSales.pngTestSales2.png

 

 

 

 

Status: Accepted
Comments
v-yuezhe-msft
Employee

@LaurentCouartou,

I have reported this issue internally: CRI 101349806, will post back once I get any updates.

Regards,
Lydia

v-yuezhe-msft
Employee
Status changed to: Accepted
 
v-yuezhe-msft
Employee

@LaurentCouartou,

I got response from PG:

"This is a known product defect that's extremely risky to fix. We have communicated this to Alberto who initially reported this issue. We also communicated to Alberto that this usage of Summarize is highly discouraged and should be replaced by alternatives using AddColumns or SummarizeColumns. Considering the high risk of a fix and low benefit of a deprecated usage pattern, we decided not to fix the issue unless there is a strong business justification for it."

Reagrds,
Lydia

powerbi_zone
Frequent Visitor

I believe we should clearly state any limitation in power bi documentation as well which is missing currently on the main page .

https://docs.microsoft.com/en-us/dax/summarize-function-dax

I have prepared a short video on just the basic usage of summarize .Hope this enables users to use this function correctly.


Do watch the below video