Forum Discussion
SUMX(VALUES(column), [Measure]) returns parent-level value, while ADDCOLUMNS/SUMMARIZECOLUMNS works
I found the root cause.
The issue was caused by the semantic model setting Value filter behavior. In my case, this setting changed how filters were evaluated in the visual context, which is why SUMX(VALUES(...), [Measure]) produced unexpected parent-level repeated values.
This also explains why:
- adding hierarchy columns changed the result
- alternative patterns such as ADDCOLUMNS / SUMMARIZECOLUMNS behaved differently
So the problem was not the base measure itself, but the semantic model's Value filter behavior setting.
Here`s the documentation.I'm mistaken. The default value hasn't changed. I remember it's changed.I can't believe it's still on the preview
Work with value filter behavior in Power BI - Power BI | Microsoft Learn
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.