Very Slow SUMX on large dataset

[robarivas]

Need help trying to make the DAX measure below, which does work, much faster.

Background: I have 2 tables (fact tables I guess) that have a many to many relationship so they are connected via a bridge table. One table is "Data" (6 million rows) the other is "Transactions" (60 million rows). The account number is how the tables are physically related. (So the bridge table [1.8 million rows] is just 1 column of unique account numbers). I need to sum up the transactions (on the transactions table) where the transaction date is earlier or equal to the payment date (on the Data table).

SUMX (

             SUMMARIZE ( Data, Data[AccountNumber], Data[Payment Date] ),

             CALCULATE ( SUM ( Transactions[Transaction Amount] ),

                                  CROSSFILTER ( Data[AccountNumber], AccountNumberBridgeTable[AccountNumber], BOTH ),

                                  Transactions[Transaction Date] <= EARLIER ( Data[Payment Date] )

                                 )

           )

[v-juanli-msft]

Hi @robarivas

The SUMX will create a filtered table and loop through it and evaluate every row and only sum the values that are fit for condition. You can try SUM instead to improve the performance.

In your secenio, both Data table and Transactions table have a single direction relationship with AccountNumberBridgeTable, right? So you use the CROSSFILTER function to change how the relationships work for just this formula.

There is an alternative way to get this goal by turning on bi-directional cross-filtering when managing the relationship.

Then modify the formula by not using the part “CROSSFILTER ( Data[AccountNumber], AccountNumberBridgeTable[AccountNumber], BOTH )”

Best Regards

Maggie

[robarivas]

Thank you @v-juanli-msft

I only use the CROSSFILTER function because I heard the bi-directional relationship makes things slower. If I use sum will my totals be correct? And how would I be able to access the payment date from the earlier context if without SUMX I lose that earlier context?