Unexpected behaviour of SUMX

[MCarmassi]

I have the following simplified model

where Table A is like this

while B and C are respectively like this

Then i have this measure defined as 

SUMX('Table B',CALCULATE(COUNTROWS('Table C'))

I would expect this measure to work like this: It iterates on the two rows of B calculating for each the number of rows of C. Given that there is no filtering between the two table, every countrows should give a result of 2, with a total of 4.

Instead, the result is 1

Apparently, the row with value 1, counts the single row of value 1 in Table C, while the row with value 3 gives blank because there are no rows with value 3 in Table C.

Basically, it behaves as if the result between Table A and Table B was bi-directional, which it is not.

Can you explain me why this happens and how to avoid it without removing too many filters ?

(in my real case i have multiple filters and slicers insisting on all three tables which I don't want to remove)

[Anonymous · Accepted solution]

Thanks for the reply from johnt75.

Hi @MCarmassi ,

When iterating on Table B using the SUMX function, the filter context of Table A affects each row of Table B. Since there is a single cross-filter relationship between Table A and Tables B and C, the filter context of Table A indirectly affects the calculation of the number of rows in Table C.

To avoid this, the ALL function can be used to remove the filter context of Table A, thus ensuring that the row count calculation of Table C is not affected. The following formula can be used:

Measure = SUMX('Table B',CALCULATE(COUNTROWS(ALL('Table C'))))

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[johnt75]

This is caused by expanded tables. I would recommend a search on https://www.sqlbi.com/articles/ for the topic, but I'll try and give you a summary as best I can.

Whenever you refer to a table in a DAX expression you are actually referring to the expanded table. An expanded table includes all the columns of the table itself, and also includes all tables at the one-side of one-to-many relationships with the base table. So in this case, the expanded table B includes all the columns from table B and all the columns from table A, as table A is on the one-side of the relationship. If there was a table D which was in a one-to-many relationship with table A then all of the columns in table D would also be in the expanded table of table B.

What this means practically is that when you iterate over table B using the SUMX, you get a row context which includes all the columns from table B and all the columns from table A. CALCULATE then transforms this row context into a filter context, and the filter on table A then reaches table C because of the relationship.

If you are going to have other filters being applied to table A which you don't want to impact then you could disable the relationship between tables A and B using CROSSFILTER

My measure =
SUMX (
    'Table B',
    CALCULATE (
        COUNTROWS ( 'Table C' ),
        CROSSFILTER ( 'Table A'[Column2], 'Table B'[Column1], NONE )
    )
)