SUMMARIZECOLUMNS behaving differently since last week

[JCole]

Hi

Anyone have any thoughts on this issue?

The SUMMARIZECOLUMNS function seems to be behaving differently in the Service compared to Desktop. I believe this has been the case since around 18 January, when many of my reports stopped working.

I isolated this to a number of measures that used the following format:

Average Result per Student =

VAR t1 =

SUMMARIZECOLUMNS ( Results[UPN], Results[Subject Id] )

VAR t2 =

    ADDCOLUMNS ( t1, "Latest", [Latest Result Points] )

VAR t3 =

    GROUPBY ( t2, Results[UPN], "Student_Average", AVERAGEX ( CURRENTGROUP (), [Latest] ) )

RETURN

    AVERAGEX ( t3, [Student_Average] )

All columns are from the 'Results' table, which is a Fact table containing assessment results for students. There are separate dimensions for 'Student Details' (linked on [UPN]) and 'Subject Details' (linked on [Subject Id]). [Latest Result Points] is a Measure which retrieves the result with the most recent date in the given context. The purpose of the main Measure is to create an average of an average, so that each student is given equal the same weighting in an overall average no matter how many subjects they have results for. This may not be the most effecient way of achieving this but it's worked OK up to last week.

I've isolated the issue to the SUMMARIZECOLUMNS statement. In the Service, it is ignoring the row context and returning all the records in the table. I've replicated this in the AdventureWorks model as follows:

1. Create the following DAX Measure: 
   SUMMARIZECOLUMNS_TEST = var t = SUMMARIZECOLUMNS(Sales[ResellerKey],Sales[ProductKey]) return countrows(t)
2. Create a Matrix visual with 'Product'[Product] as rows, 'Reseller'[State-Province] as columns, and the above SUMMARIZECOLUMNS measure as Values
3. The Matrix appears as follows:
   
4. Publish the report to the Power BI Service
5. Open the report and view the same Matrix. It appears as follows:
   

In my reports I've worked round the issue temporarily by replacing SUMMARIZECOLUMNS with SUMMARIZE in some of the Measures, but I've used this pattern a lot and the advice (from SQLBI at least) seems to be to use SUMMARIZECOLUMNS where possible.

I'm wondering whether this is related to this issue?: https://community.fabric.microsoft.com/t5/Service/Measure-in-graph-returns-inconsistent-results-when... - which then references https://www.sqlbi.com/articles/understanding-dax-auto-exist/ -  although I can't quite get my head around it. But something has definitely changed since last week.

Interestingly, if I change the test Measure to:

SUMMARIZECOLUMNS(Reseller[ResellerKey],'Product'[ProductKey]) return COUNTROWS(t)

i.e. using the keys from the Dimension tables, I get a different result, but the same in both the Desktop and the Service:

I've raised a ticket with support but they are saying it is a DAX issue so they can't help.

[JCole]

Hi Albert

The dataset is consistent on desktop and the Service - I've republished the report and semantic model, and tried moving it to a different workspace. We don't employ row level security.

As stated, I've replicated the issue with the AdventureWorks model.

Thanks

Joe

[v-heq-msft]

Hi  @JCole ,
Based on your description and the screenshots you provided of the visual objects on Power BI desktop and service, first you need to confirm that the datasets used on Power BI desktop and service are consistent. Then you need to confirm that RLS is applied.
As you have temporarily resolved the issue by using  SUMMARIZE, continue using this function where it provides the correct results.
If you still want to use SUMMARIZECOLUMNS. Please provide detailed sample data and the results you are hoping for. So that I can help you better. Show it as a screenshot or excel. Please remove any sensitive data in advance.

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly