Calculating facts with slowly changing dimensions using DAX - Performance Question

• Question

• My question primarily relates to performance.

Let’s say I have a fact table, a SCD, and a dimension table.

PaymentFacts

-PaymentId

-UserId (FK)

-PaymentAmount

-PaymentDate (FK)

UserStatus (SCD)

-UserId

-ChangeDate

-Status (FK)

-SeqNum

Statuses

-Status

Dates

-Date

My goal is to report the sum of PaymentAmount for each Status. I want to use the most recent status for each user.

Here is my measure:

Total:=CALCULATE( SUM(PaymentFacts[PaymentAmount]),

DATESBETWEEN(Dates[Date],BLANK(),LASTDATE(Dates[Date])),

FILTER(UserStatus,  UserStatus[SeqNum] = CALCULATE(MAX(UserStatus[SeqNum]),FILTER(ALL(UserStatus), UserStatus [UserId] = EARLIER(UserStatus [UserId]) && UserStatus[ChangeDate] < LASTDATE(Dates[Date]))))

)

Everything works great, but it doesn’t scale well with large datasets. Any ideas on improving it?

Thanks,

Matt

Monday, March 3, 2014 10:20 PM

Answers

• HI mhaberman,

You may have already considered this but in case you haven't...You can try looking for parts of the formula which are a fixed property of a row i.e. the value depends solely on a row's data and can't be affected by the user's selection when interacting with the model via Pivot Tables, Charts, etc. This type of logic can be off-loaded from the measure and instead persisted in a calculated column in the relevant table. You would then use the calculated column as one of your filter parameters in the CALCULATE function(s).

Javier Guillen discusses this topic in a blog post here: http://javierguillen.wordpress.com/2013/08/30/split-dax-calculation-steps-to-enhance-memory-utilization/

Regards,

Michael

Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

Website: nimblelearn.com, Blog: nimblelearn.com/blog

• Edited by Friday, March 7, 2014 7:44 AM
• Proposed as answer by Wednesday, March 19, 2014 3:03 AM
• Marked as answer by Thursday, March 27, 2014 1:48 AM
Friday, March 7, 2014 7:42 AM

All replies

• Get 64bit hardware and software.

Unrecognized Excel MVP (UEM)

Tuesday, March 4, 2014 9:21 PM
• HI mhaberman,

You may have already considered this but in case you haven't...You can try looking for parts of the formula which are a fixed property of a row i.e. the value depends solely on a row's data and can't be affected by the user's selection when interacting with the model via Pivot Tables, Charts, etc. This type of logic can be off-loaded from the measure and instead persisted in a calculated column in the relevant table. You would then use the calculated column as one of your filter parameters in the CALCULATE function(s).

Javier Guillen discusses this topic in a blog post here: http://javierguillen.wordpress.com/2013/08/30/split-dax-calculation-steps-to-enhance-memory-utilization/

Regards,

Michael

Please remember to mark a post that answers your question as an answer...If a post doesn't answer your question but you've found it helpful, please remember to vote it as helpful :)

Website: nimblelearn.com, Blog: nimblelearn.com/blog

• Edited by Friday, March 7, 2014 7:44 AM
• Proposed as answer by Wednesday, March 19, 2014 3:03 AM
• Marked as answer by Thursday, March 27, 2014 1:48 AM
Friday, March 7, 2014 7:42 AM