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!

Summarize stops working after the April 2019 update

Hi 

 

I have a calculated table which was using Summarize syntax, it was working fine before the April 2019 update.

I have another still which is not updated and it still works there.

 
This is my query..
 
Product Big Series Table =
SUMMARIZE (
'Case',
'Case'[Created On Calendar Month],
'Case'[Product BigSeries Name],
'Case'[Specific],
"Specific Count", SUM ( 'Case'[# of Cases] ),
"Product Count", CALCULATE ( SUM ( 'Case'[# of Cases] ), ALL ( 'Case'[Specific] ) ),
"Product Total", CALCULATE (
SUM ( 'Case'[# of Cases] ),
ALL ( 'Case'[Specific], 'Case'[Created On Calendar Month] )
),
"Rank", RANKX (
ALL ( 'Case'[Specific] ),
CALCULATE ( SUM ( 'Case'[# of Cases] ) ),
,
DESC
)
)
Capture.PNG
Below not working after the Update:
Capture.PNG
both the file has the same data souce.
 
Can you guide me what to do.
 
Regards,
 
Nadim
Status: Accepted
Comments
v-yulgu-msft
Employee

Hi @Anonymous ,

 

I have reported this problem internally, CRI: 115720429. Would update here once I get any response.

 

Best regards,

Yuliana Gu

v-yulgu-msft
Employee
Status changed to: Accepted
 
v-yuta-msft
Community Support

@Anonymous ,

 

The workaround is as below:

 

The problem stems from calculating [Product Total], the expected result is the sum of 'Case'[# of Cases] of all 'Case'[Product BigSeries Name], however, what SUMMARIZE do is: it groups by summarizedcolumn(in this situation, 'Case'[Product BigSeries Name], 'Case'[Created On Calendar Month], 'Case'[Specific]), and ALSO TAKE ALL COLUMNS INTO FILTER CONTEXT,  so even if we use ALL function to remove filter on 'Case'[Created On Calendar Month], and 'Case'[Specific], it still take ALL OTHER COLUMNS(not only remaining groupby columns 'Case'[Product BigSeries Name]) into account, which get wrong answer.

Here is a more detailed example of this kind of spec: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

It happened recently is because of our bug fix which changed incorrect result before and not it specifically show the answer.

Previously this issue did not happen because when calculating "Product Total", it does not take all columns of all ''Case'' into account for simplification purposes, instead it only happened to take exactly threegroupedby columns and happened the other two are removed by ALL, hence the previous answer was happened to be correct,  if table columns happen to be in a different order, the result would be different.

(https://powerbi.visualstudio.com/Analysis%20Services/_git/engine/commit/31ec52186158b828654f62b3c16567488515a4f7?path=%2FSql%2FPicasso%2FEngine%2Fsrc%2Fmd%2Fxl%2Fxlrellogop.cpp&gridItemType=2&mpath=%2FSql%2FPicasso%2FEngine%2Fsrc%2Fmd%2Fxl%2Fxlrellogop.cpp&opath=%2FSql%2FPicasso%2FEngine%2Fsrc%2Fmd%2Fxl%2Fxlrellogop.cpp&mversion=GC31ec52186158b828654f62b3c16567488515a4f7&oversion=GC41fdbb09d91abf1b0f2a989f2d21aabb5f4e1d20&_a=compare

However, after bug fixes, it correctly take all needed columns into account according to DAX spec, hence triggering this issue.

 

What we suggest customer do is to use ALLExcept function. That is, to calculate subtotal based only on 'Case'[Product BigSeries Name], just write ALLExcept ( 'Case'[Product BigSeries Name]) to explicitely remove all other filters.

 

and for "Specific Count", use   "CALCULATE (SUM ( 'Case'[# of Cases] ),ALLEXCEPT('Case'[Created On Calendar Month],'Case'[Product BigSeries Name],'Case'[Specific]))"

for "Product Count"  use   "CALCULATE (SUM ( 'Case'[# of Cases] ),ALLEXCEPT('Case'[Created On Calendar Month],'Case'[Product BigSeries Name]))"

 

Regards,

Jimmy Tao