Forum Discussion
Calculate with multiple tables
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.
That sounds like a measure like:
Amount = SUMX(FILTER(tablename,tablename[isnew]=1),[statement_amount])
tablename is the name of your table
isnew is your column that is either 1 or 0
You can put that measure in a table with date_created and have the sums for each date or you could use a date slicer for date_created or statement_date and have the Amount in a card and you could see the amount as you select a date, or you could put date as the x-axis of a line graph and graph out the amount per date. What exactly are you looking to do?
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. :)
