Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am attempting to model recovery of outstanding debts based on a number of factors:
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 )
)
Hi @jo_clifford ,
Your formulas are quite complicated, maybe you could try to combine some of them. Please take a look at below topics.
https://community.powerbi.com/t5/Desktop/Improve-Performance-Measure-DAX/m-p/1410068
https://community.powerbi.com/t5/Desktop/DAX-Measure-Reference-Performance/m-p/1368008
Best Regards,
Jay
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
77 | |
69 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |