Poor performance of Debt Recovery Modelling DAX me...

jo_clifford · ‎11-18-2020

Hi,

I am attempting to model recovery of outstanding debts based on a number of factors:

projected recovery amount of each debt type
projected payment timeframe of each debtor
start date of the debt recovery project
the ranking order in which the recovery of debts should be actioned
what if parameters for:
- Recovery Cost per hour
- Average Hrs Effort per debt
- Number of staff allocated to the debt recovery project.

I am able to generate the desired visual with my test record set which contains 100 records.
However when attempting to use my actual record set of > 100K records, the visual exceeds the available resources.

I would really appreciate some guidance on how to improve my DAX measures for faster performance.
It is my understanding that I cannot leverage calculated columns due to the use of what if parameters in the model.

Below are the DAX measures upon which the Cumulative Recovery by Date Worked measure in the line chart visual relies.
pbix link

Many thanks in advance
Jo.

Total Paid = 
CALCULATE( SUM( DimWorkAttribute_A[Median Paid] ),
            TREATAS( VALUES(FactWork[WorkTypeKey] ), DimWorkAttribute_A[WorkTypeKey] 
)

Recovery Cost Per Work Unit = 
IF( ISBLANK( [Total Paid] ),
    BLANK(),
    [_Average Hours Effort Per Work Unit Value] * [_Recovery Cost Per Hr Value]
)

Projected Recovery = 
[Total Paid] - [Recovery Cost Per Work Unit]

Total Hrs Effort Per Work Day = 
VAR FTEHrsPerDay = 8
VAR AllocatedFTEResources = [_Number of Team Members Value]
VAR TotalEffortPerDay = FTEHrsPerDay * AllocatedFTEResources 
RETURN
TotalEffortPerDay

Work Item Number Of Work Days From Start = 
VAR WorkEffort = [_Average Hours Effort Per Work Unit Value] 
VAR HoursPerDay = [Total Hrs Effort Per Work Day]
VAR WorkDayNumber = ROUNDUP( 
                        MAXX( DimWorkRank, 
                            DIVIDE( DimWorkRank[WorkRank] * WorkEffort, HoursPerDay ) 
                        ),
                        0
                )
RETURN 
WorkDayNumber

Cumulative Work Days = 
VAR CurrentWorkDate = MAX( DimDate[Date] )
VAR DateFilter = FILTER ( 
                         ALLSELECTED( DimDate ),
                         DimDate[Date] <= CurrentWorkDate ) 
RETURN
CALCULATE( SUM( DimDate[IsWorkDay] ), 
            DateFilter 
)

Projected Date to Start Work Unit = 
VAR ProjectDates = ALLSELECTED( DimDate )
VAR ProjectedWorkStartDate = CALCULATE(
                                    MIN( DimDate[Date] ),
                                    FILTER( ProjectDates,
                                    [Cumulative Work Days] = [Work Item Number Of Work Days From Start]
                                    )
                            )
RETURN 
IF( ISBLANK( [Work Item Number Of Work Days From Start] ),
    BLANK(),
    ProjectedWorkStartDate
)

Projected Recovery by Date Worked = 
VAR WorkDate = SUMMARIZE( DimWorkRank, DimWorkRank[WorkRank], "ProjectedRecovery", [Projected Recovery], "WorkDate", [Projected Date to Start Work Unit] )
VAR DateTable = SUMMARIZE( DimDate, DimDate[Date], "WorkDate", MAX( DimDate[Date] ) )
VAR SummaryTable = NATURALINNERJOIN( DateTable, WorkDate ) 
RETURN
SUMX( SummaryTable, [ProjectedRecovery] )

Cumulative Projected Recovery by Date Worked = 
VAR CurrentDate = MAX( DimDate[Date] )
VAR DateFilter = FILTER( ALLSELECTED(DimDate), DimDate[Date] <= CurrentDate )
RETURN
IF( ISBLANK( [Projected Recovery by Date Worked] ),
    BLANK(),
    CALCULATE( [Projected Recovery by Date Worked], DateFilter )
)