Forum Discussion
Measure Optimization Help
looking at the current code I have 2 observations:
1) you use nested iterator functions (FILTER nested in COUNTAX) - this is heavy lifting performance wise
e.g. here (taken from your model)
Rough_Pass = COUNTAX(FILTER(RoughInspections_merged, RoughInspections_merged[RoughInspectionStatus]="Pass"), RoughInspections_merged[RoughInspectionStatus])
this should give exactly same numbers, with much better performance
Rough_Pass= CALCULATE ( COUNTA ( RoughInspections_merged[RoughInspectionStatus] ), RoughInspections_merged[RoughInspectionStatus] = "Pass" )
2) you calculate a new table in Memory to just do sum based on filter - you don't need to calculate table for that
I'd try the following:
Item1_FAIL = CALCULATE ( COUNTA ( 'DataTable1'[Item1] ), 'DataTable1'[Item1] = "Fail" )
also you can have a look at this video, it's quite helpful on where to start optimization
https://www.sqlbi.com/tv/optimizing-dax-queries/
Aha! Thanks Stachu, I appreciate the specific suggestion. I've just started changing the code over to use CALCULATE(COUNTA() instead of COUNTAX(FILTER() and it appears the time to load after a slicer change has already improved. I'll follow up once I have all the code changed.
Another question - I'm calculating population statistics to compare to the sliced stats, like this:
Population Rough Total Failures = COUNTX(FILTER(ALL(RoughInspections_merged), RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier]="Fail"), RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier])
Any suggestion for how to rework/optimize this using the pattern that Stachu suggested? CALCULATE(COUNTA(...
I've tried this but it's clearly not right:
CALCULATE(COUNTA(ALL(RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier])), ALL(RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier]="Fail"))
if you add ALL inside COUNTA it counts all
CALCULATE ( COUNTA ( RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] ), FILTER ( ALL ( RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] ), RoughInspections_merged[RoughAirBarrier.CantileverAirBarrier] = "Fail" ) )