Count if two columns are equal and a third not

[maryjanesmith]

Hi everyone!

My goal is to count the tickets that reopen for the same reason. But I have duplicates because of the granularity of the table so I cannot count these records.  In order to do that I only want to count records that are the same for idticket and idclient but have a different open date.

In this example (table above) the count should be 2:
idticket 12, idclient 1,18/07
idticket 12, idclient 6, 5/12

idticket idclient Date

12	001	12/07/2021
12	001	12/07/2021
12	001	12/07/2021
12	001	18/07/2021
12	006	04/12/2021
12	006	05/12/2021
12	100
13	007
13	020	14/07/2021
14	007	13/07/2021
14	013	01/07/2021
14	013	01/07/2021
17	006
22	050

Pbix file: https://1drv.ms/u/s!AuMLcKZkL7PFgkKbiP_ddk_0CnNR?e=4mt9JI

Thank you in advance 🙂

[maryjanesmith]

Hi
Thank you for your answer! but in my report (not the example file) when I try to do the same I have this error. This happens inside the earlier function:

[Samarth_18]

@maryjanesmith You are creating a column not a measure right?

[maryjanesmith]

Yes, I created a column, not a measure.

[Samarth_18]

@maryjanesmith can you share the snapshot of your code and can you please check the attached file for reference.

https://we.tl/t-uEGmsxJNik 

[maryjanesmith]

Sorry my mistake, I put the column in the wrong table. But there is one problem, for example, In this case below, the count should be 4 and not 5. The line with date 13/07 is repeated and should only be counted once 😕  I updated the file with this example and the table 2 with more examples. @Samarth_18 can you help?
 https://1drv.ms/u/s!AuMLcKZkL7PFgkKbiP_ddk_0CnNR?e=cwfOF5

idticketidclientDate_timeColumn

12	001	12/07/2021	0
12	001	12/07/2021	0
12	001	12/07/2021	0
12	001	13/07/2021	1
12	001	13/07/2021	1
12	001	14/07/2021	1
12	001	18/07/2021	1
12	001	19/08/2021	1

[maryjanesmith]

This is my code of the calculated column: I just replaced your code with my real tables and columns:

[Samarth_18]

@maryjanesmith something is really missing. Is it possible for you to share PBIX file after removing sensitive data?

[Samarth_18]

Hi @maryjanesmith ,

You can create a column with below code and use it as filter on your visual:-

Column =
VAR result =
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[idclient] = EARLIER ( 'Table'[idclient] )
                && 'Table'[idticket] = EARLIER ( 'Table'[idticket] )
                && 'Table'[Date_time] < EARLIER ( 'Table'[Date_time] )
        )
    )
RETURN
    IF ( result <> 0, 1, 0 )

Output:-

Updated file below:-

https://we.tl/t-uEGmsxJNik 

Thanks,

Samarth