cannot summarize number field

[Anonymous]

Hello,

I created a star model in SSAS using SSDT. When I connect to SSAS using PowerBI Desktop I can't add the number fields to the values of i.e. a pivot table. The Sigma sign is missing so PBI does not recognize the field as a number.

However, when i go check te properties of the field in VS everything seems to be in order:

Propertiesof the field

The result in PowerBI:

The SSAS instance is set to Tabular on SQL 2019. Compatiabilty is set to 1500.

I'm at a total loss, please help.

Edit: I forgot to mention that using the field in a cumstom measure works just fine. I just don't wont to create duplicates of all my number fields just so I can sum them.

[Piquet65]

Have the same probl;em that has cropped up in our Tabular 2019 model - all numeric fields now no longer aggregate & do not have the Sigma icon next to them in Power BI fields list.

Have deployed this tabular model to 2x environments (Dev & Test environments) with the same issue.

Browsing the model using SSMS (latest version) also exhibits the behaviour - i.e. numeric fields instead of summing act like dimensional attributes, showing each distinct value in a table (for example). Browsing the model using SSMS also exhibits this unwanted behaviour. 

We have no idea what caused this issue - previously the model was functioning correctly. Looks like we'll need to rebuild the Tabular model from scratch (as per the suggested solution/workaround) - perhaps we will discover what caused the problem as part of that exercise...

[Piquet65]

I found the cause of this issue: Calculation Groups.

We added a Calculation Group to the model (for Time Intelligence functions such as YTD, etc) and a side-effect of adding a calculation group is that it sets the model level property "Discourage implicit measures" to TRUE (see Solved: Tabular Editor - Calculation groups - Microsoft Power BI Community).

This "hidden" model property (not exposed in SSDT) essentially removes the Sigma icon from all numeric fields and they are from that point treated as dimensional attributes, i.e. instead of a "Sales Amount" field showing Sum(Sales Amount) in Power BI a list of DISTINCT(Sales Amount) values is shown instead.

Removing the Calculation Group from the model does not revert back to teh previous behaviour ([Discourage implicit measures] remains set to TRUE). In order to revert back to the behaviour prior to adding a Calculation Group, edit the model.bim file directly (i.e. the JSON), find the "discourage..." property and change it from TRUE to FALSE.

A WARNING message in SSDT when creating a Calculation Group regarding this side-effect would be a good idea - as would exposing this model-level property in SSDT so that it can be modified (or at least viewed).

[Anonymous]

I had the same issue and this solved it, thanks a lot!

[Venema-BI]

This is the right awnser here.

[Anonymous · Accepted solution]

I Solved the issue by removing the project from my sollution and creating a new project.  I was able to quickly create this project because most of the ETL was done in SSIS.

Unfortunately, I was unable to determine the cause of this issue.

[v-deddai1-msft]

Hi @Anonymous ，

I have test it in my environment,It works well. I really can't reproduce your issue. Would you please show us what's the column data type shown in Power BI desktop? And would you please try to use the latest version of Power BI desktop to connect to it?

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , I think I saw some also complained about no summarization.

Can you check and log a ticket at : https://community.powerbi.com/t5/Issues/idb-p/Issues