Forum Discussion
Solved
Calculate with multiple tables
I can't seem to figure out how to calculate between two tables. I have two dates in a single table, statement_date and date_created. If the two match, it's a new customer. If not, existing customer. ...
Greg_Deckler, the SUMX( FILTER() ) idiom you've suggested is very much a performance antipattern.
The preferred construction would be:
CALCULATE( SUM( 'Table'[statement_amount] ) ,'Table'[isnew] = 1 )The construction utilizing SUMX( FILTER() ) would have to iterate the fact table twice to achieve a result. First FILTER() would step through every row in the fact table performing the test against [isnew]. It doesn't matter that we know there are only two possible values of [isnew] to test, FILTER(), by design tests every row of its input table.
After the FILTER() returns, the resultant table (some significantly smaller subset of the fact table) would then be iterated again.
Make sure to tell Microsoft since that comes directly from their SUMX example. :)
