Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Carrying on from my previous question here
I still do not understand why pbi desktop table visual not giving the same result as per daxstudio.
Replicated test
-- parent - child rollup test
-- 3 rows, 3 values, sumx
EVALUATE
--RollupTest =
DATATABLE (
"name", STRING,
"child value", INTEGER,
{
{ "test1", 1 },
{ "test2", 3 },
{ "test3", 1 }
}
)
EVALUATE
--rollup test =
VAR _rollup =
SUMX (
CALCULATETABLE (
'RollupTest'
),
'RollupTest'[child value]
)
RETURN
{ _rollup }
Daxstudio
Yep, we get 5 (1+3+1) correct
PBI Desktop
Nope, the [rollup test] column does not return 5 for each of the 3 rows. Why?
No doubt there is a missunderstanding in my "row context" knowledge. But to me the [rollup test] calculated column clearly states:
1) hit the entire 'rollup test' table again for each row
2) sum that temporary table
Which gives result of 5 for each of the 3 row iterations. Does it not?
Solved! Go to Solution.
Hey @garythomannCoGC ,
This measure
rollup test =
VAR _rollup =
SUMX (
// CALCULATETABLE (
// ALLSELECTED(
'RollupTest'
// )
// )
, 'RollupTest'[child value]
)
RETURN
{ _rollup }
returns this:
If you look closely, you will realize that I removed the CALCULATETABLE, but at the same I have added ALLSELECTED inside the CALCULATETABLE.
When
SUMX( <table> , ...
is used inside a calculated column "<table>" is reflecting the complete table because there is no filter context (at least not at the beginning).
When
SUMX( CALCULATETABLE(<table>) , ...
is used inside a calculated column: CALCULATETABLE is transforming the existing row context into a filter context; as the row context is defined by all columns, the filtered table contains only a single row, hence the SUMX returns the value of the current row.
When
SUMX( CALCULATETABLE(ALLSELECTED(<table>)) , ...
is used inside a calculated column: ALLSELECTED is returning ALL rows for each iteration of SUMX.
All the above is described here: MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering (mdxdax.blogspot.com), please be warned, this article is not an easy one.
One of the many essential parts inside of this article is the "four-step" execution, which is performed whenever there is a CALCULATE or a CALCULATETABLE.
Hopefully, this provides some additional insights.
Regards,
Tom
Hey @garythomannCoGC ,
This measure
rollup test =
VAR _rollup =
SUMX (
// CALCULATETABLE (
// ALLSELECTED(
'RollupTest'
// )
// )
, 'RollupTest'[child value]
)
RETURN
{ _rollup }
returns this:
If you look closely, you will realize that I removed the CALCULATETABLE, but at the same I have added ALLSELECTED inside the CALCULATETABLE.
When
SUMX( <table> , ...
is used inside a calculated column "<table>" is reflecting the complete table because there is no filter context (at least not at the beginning).
When
SUMX( CALCULATETABLE(<table>) , ...
is used inside a calculated column: CALCULATETABLE is transforming the existing row context into a filter context; as the row context is defined by all columns, the filtered table contains only a single row, hence the SUMX returns the value of the current row.
When
SUMX( CALCULATETABLE(ALLSELECTED(<table>)) , ...
is used inside a calculated column: ALLSELECTED is returning ALL rows for each iteration of SUMX.
All the above is described here: MDX and DAX topics: The Logic behind the Magic of DAX Cross Table Filtering (mdxdax.blogspot.com), please be warned, this article is not an easy one.
One of the many essential parts inside of this article is the "four-step" execution, which is performed whenever there is a CALCULATE or a CALCULATETABLE.
Hopefully, this provides some additional insights.
Regards,
Tom
User | Count |
---|---|
119 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
177 | |
84 | |
70 | |
64 | |
54 |