SUMX(VALUES(column), [Measure]) returns parent-level value, while ADDCOLUMNS/SUMMARIZECOLUMNS works

This is interesting. Can you share your PBIX file, or can you create a sample dataset which replicates the problem ? You can share a link via GoogleDrive, OneDrive etc.

B & C:

SUMX iterates over VALUES(dim_reportingline[mr]) but the measure evaluates against the outer visual filter context rather than each iterated row, because the column is already filtered externally and the implicit context transition has nothing new to apply.

D & E:

ADDCOLUMNS and SUMMARIZECOLUMNS materialize the measure value per MR row eagerly during table construction, before the outer SUMX iterates, so each value is correctly scoped to its MR before any outer context can interfere.

DAX Studio Query Plan is the right tool, you could compare the LogicalQueryPlan nodes between Test 2 and Test 4.

Thanks for the suggestion.

Unfortunately, I cannot share the actual model or sample data because this is based on internal business data. At the moment, I have also not been able to reproduce the issue with a small simplified dataset.

What I can add is the following:

• I analyzed the Storage Engine execution using SQL Profiler.
• For the problematic rows, the translated SQL generated for the row-level evaluation appears to be effectively the same.
• However, the final behavior in the visual is clearly different depending on the DAX pattern.
• Because of that, I suspect this may be related to a Formula Engine optimization/evaluation issue rather than a simple semantic misunderstanding.

At a high level, the model contains three related tables:

• a Date table
• a Fact table
• an organizational hierarchy dimension table

The Date table is related to the Fact table in a one-to-many relationship.
The hierarchy dimension table is also related to the Fact table in a one-to-many relationship.
The hierarchy table is stored as a monthly snapshot of the organizational structure.

I also tested another variation: replacing VALUES() with SUMMARIZE().

However, the same issue still appears as long as the corresponding hierarchy field is already present in the table visual.

What seems to happen is this:

• if the visual already contains a given hierarchy field
• and I iterate/group again by that same field in the measure
• then that field seems to be effectively ignored during evaluation
• and the result becomes the aggregated value with that same field removed/ignored

In contrast, if I use another field with the same key (for example, a different column representing the same entity/key), the result displays correctly.

So this does not look like a simple VALUES()-only issue. It looks more like a problem triggered when the visual already contains the same grouping field that is being used again inside the measure.

Also, in the screenshots I posted earlier, the result changes after adding additional hierarchy columns to the visual. That behavior is clearly not expected, because adding more dimension attributes should not cause each lower-level row to display the same parent-level value.

So at this point, although I cannot share the underlying data, the combination of:

• SUMX(VALUES(...), [Measure]) returning repeated parent-level values
• the same issue still appearing when VALUES() is replaced with SUMMARIZE()
• the result changing when the same hierarchy field is already present in the visual
• using another column with the same key working correctly
• and SQL Profiler showing effectively identical SE-side SQL

makes me suspect a Formula Engine issue, or at least an FE optimization edge case, when evaluating iterators/grouping over a field that is already part of the visual context.

If helpful, I can still provide a more detailed logical description of the model shape and the exact comparison measures I tested.

Hi Xian-Zuo 

answering this without seeing the model is impossible. Can you share the pbix or provide tables with fake data so we can replicate?

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hi Xian-Zuo please share sample pbix files, happy to dig into thi with you.

I found a more specific trigger for this issue.

The abnormal behavior appears when the filter context contains two <> conditions. In that case, SUMX starts returning incorrect results in the visual.

It looks as if the related hierarchy field is being ignored during evaluation, and the measure returns the aggregated value with that field effectively removed from the grouping context.

I have included the related TMDL and screenshots for reference.

createOrReplace

	model Model
		culture: en-US
		defaultPowerBIDataSourceVersion: powerBI_V3
		sourceQueryCulture: en-us
		dataAccessOptions
			legacyRedirects
			returnErrorValuesAsNull

		table fact
			lineageTag: 7fca3ae8-7286-4796-a52a-9d10adafc299

			measure 'SUMX value' = SUMX(VALUES('dim'[MR]),[Sum Value])
				formatString: 0
				lineageTag: 28e43acd-668a-4722-b6e0-67e04b7cf220

			measure 'Sum Value' = SUM('fact'[value])
				formatString: 0
				lineageTag: f2b30ab8-41f6-40ce-8838-7cbf0ef9dde2

			column AD
				dataType: string
				lineageTag: 75a1d930-1f9c-46d3-bac5-bd770baddfb8
				summarizeBy: none
				sourceColumn: AD

				annotation SummarizationSetBy = Automatic

			column DM
				dataType: string
				lineageTag: 87c6d205-70e8-4f2a-b061-f9002ea107b4
				summarizeBy: none
				sourceColumn: DM

				annotation SummarizationSetBy = Automatic

			column RM
				dataType: string
				lineageTag: 6ce4ef2d-915b-4254-84ca-5c2d01b31e1e
				summarizeBy: none
				sourceColumn: RM

				annotation SummarizationSetBy = Automatic

			column MR
				dataType: string
				lineageTag: 97bfc23f-497a-4bde-b20f-f6d32b8a93da
				summarizeBy: none
				sourceColumn: MR

				annotation SummarizationSetBy = Automatic

			column Type
				dataType: string
				lineageTag: 78f19ce8-8760-499f-886c-63604c9b2710
				summarizeBy: none
				sourceColumn: Type

				annotation SummarizationSetBy = Automatic

			column value
				dataType: int64
				formatString: 0
				lineageTag: 3114c629-3155-4539-97be-13f85ef0979d
				summarizeBy: sum
				sourceColumn: value

				changedProperty = DataType

				annotation SummarizationSetBy = Automatic

			column yearmonth
				dataType: string
				lineageTag: c2c8cf03-ce1c-4a92-a7e7-bcec58fcaef8
				summarizeBy: none
				sourceColumn: yearmonth

				annotation SummarizationSetBy = Automatic

			column key = [yearmonth]&"_"&[MR]
				lineageTag: 95208981-759c-433c-924d-7622c400bd49
				summarizeBy: none

				annotation SummarizationSetBy = Automatic

			partition fact = m
				mode: import
				source =
						let
						    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7dO9CoQwDAfwd+msoGkr9xLncKs4FHqDoB4o3PObM/iBl9Z279J0yI+0/EnTCCigKkqRCWPzX7HDWiYqw7SW5wuPUoo2CwCwA7gBQEDeAyAgCahYoDegwvqrY4Dz0yfRjV/TdxZv9WfMZ9O/Z7xreZl2tFlyyBUNfLADITYWH2BjYcE1Fu1+U0AuPsDl4uvnc3EKPhe0fzw6mrQxaWPSxoRE0y4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [yearmonth = _t, AD = _t, DM = _t, RM = _t, MR = _t, Type = _t, value = _t])
						in
						    Source

			annotation PBI_ResultType = Table

			annotation PBI_NavigationStepName = Navigation

		table dim
			lineageTag: 82602666-1dbc-4d81-b193-a7d89f2a659d

			column AD
				lineageTag: 9843a712-e08a-4699-bb8f-6c22f6dcb7e3
				summarizeBy: none
				isNameInferred
				sourceColumn: fact[AD]
				columnOrigin: fact.AD

				annotation SummarizationSetBy = Automatic

			column yearmonth
				lineageTag: eb88c63c-e204-4611-a676-8a7f38962004
				summarizeBy: none
				isNameInferred
				sourceColumn: fact[yearmonth]
				columnOrigin: fact.yearmonth

				annotation SummarizationSetBy = Automatic

			column RM
				lineageTag: 93931e7f-a6c9-4b2e-ac19-159c553e7894
				summarizeBy: none
				isNameInferred
				sourceColumn: fact[RM]
				columnOrigin: fact.RM

				annotation SummarizationSetBy = Automatic

			column DM
				lineageTag: 47734e27-6228-4551-a8e0-31a4856a942f
				summarizeBy: none
				isNameInferred
				sourceColumn: fact[DM]
				columnOrigin: fact.DM

				annotation SummarizationSetBy = Automatic

			column MR
				lineageTag: 2edc28cb-a1c9-45b4-a3b3-e2774fc61aa1
				summarizeBy: none
				isNameInferred
				sourceColumn: fact[MR]
				columnOrigin: fact.MR

				annotation SummarizationSetBy = Automatic

			column key
				lineageTag: 74299008-c329-487e-a46d-b4a71f740422
				summarizeBy: none
				isNameInferred
				sourceColumn: [key]

				annotation SummarizationSetBy = Automatic

			column Type
				lineageTag: 16cfe588-e599-4b48-8695-57939be91ad8
				summarizeBy: none
				isNameInferred
				sourceColumn: fact[Type]
				columnOrigin: fact.Type

				annotation SummarizationSetBy = Automatic

			partition dim = calculated
				mode: import
				source =
						SUMMARIZE('fact'
						    ,[yearmonth]
						    ,[AD]
						    ,[RM]
						    ,[DM]
						    ,[MR]
						    ,[Type]
						    ,"key",[yearmonth]&"_"&[MR]
						)

			annotation PBI_Id = 0c229d07a92d4a74bbef2f943e36314b

		relationship 985514e0-0594-2b22-7af3-873d4de14e98
			fromColumn: fact.key
			toColumn: dim.key

		cultureInfo en-US

		annotation __PBI_TimeIntelligenceEnabled = 0

		annotation PBI_QueryOrder = ["fact"]