Optimizing performance when using time comparison

Chris2016 — Thu, 30 Mar 2023 14:45:30 GMT

Hello,

I have a large dataset where some of the data looks like this:

Name	Test	Activity	DateStarted	DateCompleted	PercComplete	Key	_TestCompleteDate	_CompletedIndividual	_TestCompleteUpToLastMonth	_TestCompleteUpToLastMonth2
A	x	1	03/02/2022		25%	A-x
A	x	2	03/02/2022		25%	A-x
A	x	3	03/03/2022	04/04/2022	25%	A-x
A	x	4	06/03/2022		25%	A-x
A	y	1	04/02/2022	04/02/2022	100%	A-y	04/05/2022	A	1	1
A	y	2	04/02/2022	04/02/2022	100%	A-y	04/05/2022	A	1	1
A	y	3	04/05/2022	04/05/2022	100%	A-y	04/05/2022	A	1	1
B	z	1	10/02/2022		60%	B-z
B	z	2	10/02/2022	10/02/2022	60%	B-z
B	z	3	10/02/2022	10/02/2022	60%	B-z
B	z	4	10/03/2022	10/13/2022	60%	B-z
B	z	5	10/05/2022		60%	B-z
C	x	1	10/10/2022	10/11/2022	100%	C-x	10/13/2022	C	1	1
C	x	2	10/12/2022	10/12/2022	100%	C-x	10/13/2022	C	1	1
C	x	3	10/12/2022	10/12/2022	100%	C-x	10/13/2022	C	1	1
C	x	4	10/12/2022	10/13/2022	100%	C-x	10/13/2022	C	1	1
C	y	1	10/13/2022	10/13/2022	100%	C-y	03/29/2023	C
C	y	2	10/13/2022	10/13/2022	100%	C-y	03/29/2023	C
C	y	3	10/29/2022	03/29/2023	100%	C-y	03/29/2023	C
D	z	1	01/12/2023	01/12/2023	40%	D-z
D	z	2	01/12/2023	01/12/2023	40%	D-z
D	z	3	02/01/2023		40%	D-z
D	z	4	02/01/2023		40%	D-z
D	z	5	02/01/2023		40%	D-z

Where
Columns:

PercComplete = DIVIDE(CALCULATE(COUNTA('Table'[DateCompleted]), ALLEXCEPT('Table','Table'[Key])), CALCULATE(COUNTA('Table'[Activity]), ALLEXCEPT('Table','Table'[Key])), 0)

_CompletedIndividual = IF([PercComplete]=1, [Name], BLANK())

_TestCompleteDate = IF([PercComplete]=1, CALCULATE(MAX([DateCompleted]), ALLEXCEPT('Table','Table'[Key])))

_TestCompleteUpToLastMonth = IF([_TestCompleteDate]<=TODAY()-30 && [_CompletedIndividual]<>BLANK(),1)

& measure

TestCompleteIndividuals = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[_TestCompleteUpToLastMonth]=1))

or column:

_TestCompleteUpToLastMonth2 = 
var maxcompldate =CALCULATE(MAX([_TestCompleteDate]), ALLEXCEPT('Table','Table'[Key]))
return IF(maxcompldate<=TODAY()-30 && [_CompletedIndividual]<>BLANK(),1)

& measure:

TestCompleteIndividuals2 = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[_TestCompleteUpToLastMonth2]=1))

What I am trying to achieve is calculate how many "Name"-s have completed all the "Activities" of a "Test" up until 30 days ago (measures TestCompleteIndividuals and TestCompleteIndividuals2).
A basic calculation of who completed all the activities is working just fine with the report visuals, but when I add the TestCompleteIndividuals and TestCompleteIndividuals2 measures working with [_TestCompleteDate]<=TODAY()-30 or maxcompldate<=TODAY()-30, the visuals that contain any of these measures take so long to load, they sometimes crash.

Is there a way to calculate how many "Name"-s have passed all the activities in a test up until a month ago that will not burden the report performance?

Many thanks for any input on the matter.

Re: Optimizing performance when using time comparison

tamerj1 — Thu, 30 Mar 2023 15:51:00 GMT

Chris2016

Please try

TestCompleteIndividuals =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
FILTER ( 'Table', 'Table'[_TestCompleteUpToLastMonth] = 1 ),
"@Name", 'Table'[Name]
)
)
)

Re: Optimizing performance when using time comparison

Chris2016 — Fri, 31 Mar 2023 08:40:40 GMT

Thanks a lot for your help, Tamer, this is great. The visuals no longer have issues loading.
Any idea why this measures has performance issues in the given context as opposed to your approach?

TestCompleteIndividuals = CALCULATE(DISTINCTCOUNT('Table'[Name]),FILTER('Table','Table'[_TestCompleteUpToLastMonth]=1))

Thanks, and best regards!

Re: Optimizing performance when using time comparison

tamerj1 — Fri, 31 Mar 2023 09:08:59 GMT

Chris2016
Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI

Re: Optimizing performance when using time comparison

Chris2016 — Fri, 31 Mar 2023 09:12:50 GMT

"... counting unique values in complex reports can still create performance issues. The main reason for this is that DISTINCTCOUNT is a non-additive aggregation that must be computed for every cell in the report. Understanding the behavior of this aggregation can explain why other equivalent expressions that are slower in theory, can provide better performance in specific reports."
This is great, thanks, tamerj1 !

topic Optimizing performance when using time comparison in DAX Commands and Tips

Optimizing performance when using time comparison

Re: Optimizing performance when using time comparison

Re: Optimizing performance when using time comparison

Re: Optimizing performance when using time comparison

Re: Optimizing performance when using time comparison