Forum Discussion
cannot summarize number field
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.
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...
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).