Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
garythomannCoGC
Impactful Individual
Impactful Individual

power bi deskstop - calculated column result different in daxstudio

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

garythomannCoGC_0-1716947162708.png

Yep, we get 5  (1+3+1)  correct

 

PBI Desktop

garythomannCoGC_0-1716950983975.png

 

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?

 

 

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

Hey @garythomannCoGC ,

 

This measure 

 

rollup test = 
VAR _rollup =
    SUMX (
        // CALCULATETABLE (
            // ALLSELECTED(
                'RollupTest'
        //     )
        // )
        , 'RollupTest'[child value]
    )
RETURN
    { _rollup }

 

returns this:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

Hey @garythomannCoGC ,

 

This measure 

 

rollup test = 
VAR _rollup =
    SUMX (
        // CALCULATETABLE (
            // ALLSELECTED(
                'RollupTest'
        //     )
        // )
        , 'RollupTest'[child value]
    )
RETURN
    { _rollup }

 

returns this:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
garythomannCoGC
Impactful Individual
Impactful Individual

This question also posted  stack overflow  

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.