Help Optimizing Calculated Column & Measure: count records based on columns

[maryjanesmith]

Hi
I need to count the records that have the same client and the same ticket but different open dates which means I am only interested in cases of clients that reopen the ticket for the same reason. In the case below the count should be 12.

idticket idcliet Date

12	001	12/07/2021
12	001	12/07/2021
12	001	13/07/2021
12	001	14/07/2021
12	001	13/07/2021
12	001	12/07/2021
12	001	18/07/2021
12	001	19/08/2021
12	006	04/12/2021
12	006	05/12/2021
12	100	06/11/2021
13	020	14/07/2021
14	007	13/07/2021
14	013	01/07/2021
22	050	05/08/2021

I wrote this calculated column:

var valorActualConta='Table'[idclient]
var valorActualOcorrencia='Table'[idticket]
var valorActualTempo='Table'[Date_Time]
return calculate(DISTINCTCOUNT('Table'[Date_Time] ),FILTER(ALL('Table'),'Table'[idclient] = valorActualConta && 'Table'[idticket] = valorActualOcorrencia ))

and then I wrote a measure to sum all the cases:

sumx(SUMMARIZE('Table','Table'[idclient],'Table'[idticket],"max",max('Table'[Reincidências])),[max])

The file is here https://1drv.ms/u/s!AuMLcKZkL7PFgkY3qjc2GJ35giM2?e=focqp7

The problem is that the real table has almost 2 million rows and the calculated column is waiting...until now.

Can you help, please?

Thank you

[maryjanesmith]

Thank you, yes it is much better!

[bcdobbs · Accepted solution]

Can you see if this works any better as the calculated column?

Reincidências = 
CALCULATE(
    DISTINCTCOUNT('Table'[Date_Time] ),
    ALLEXCEPT ( 'Table', 'Table'[idclient], 'Table'[idticket] )
)

If not I'll think about a more fundamental change!