Optimizing performance when using time comparison

[Chris2016]

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.

[Chris2016]

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!

[tamerj1]

@Chris2016 
Analyzing the performance of DISTINCTCOUNT in DAX - SQLBI

[Chris2016]

"... 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 !

[tamerj1 · Accepted solution]

@Chris2016 

Please try

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