Forum Discussion
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. There's a slicer on the report page using statement_date... but my formula does not return any values. Any tips?
Sales New Cust = CALCULATE(sum('account_payment'[statement_amount]),
filter(
all('account_payment'),
'account_payment'[date_created]=DATE(year('account_payment'[statement_date]),month('account_payment'[statement_date]),day('account_payment'[statement_date]))))
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.
8 Replies
Seems like you are unnecessarily complicating your formulas here. You could create a new column in your table like:
Column = IF([Date1] = [Date2],1,0)
1 is a new customer, 0 is not a new customer.
Sales New Cust = CALCULATE(sum('account_payment'[statement_amount]),
filter(
all('account_payment'),
Column = 1)I'd have to see your model to know if this is correct, you might need a RELATED in there, etc. Sample data and model would assist.
That works as a standalone, but what suggestion do you have for a date column that's linked to a slicer on the same report tab?
Can you provide some more details? I am having trouble visualizing your data and relationships. Is that date column you mention in a date table and are your tables related to one another?
