Forum Discussion
count rows with some conditions
Hi everyone!
I need to count the rows with same idticket, idclient after the first time that they appear. I also have to exclude duplicates so I only have to count once these records. The result is indicated below. so the total in this example should be 10. Can you help, please?
PBI file https://1drv.ms/u/s!AuMLcKZkL7PFgkY3qjc2GJ35giM2?e=aCetar
thank you!
17 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
It is for creating a measure.
test 2 fix =
SUMX (
'Table',
CALCULATE (
SWITCH (
TRUE (),
VAR currentidticket =
MAX ( 'Table'[idticket] )
VAR currentidclient =
MAX ( 'Table'[idclient] )
RETURN
MINX (
FILTER (
ALL ( 'Table' ),
'Table'[idticket] = currentidticket
&& 'Table'[idclient] = currentidclient
),
'Table'[Date_time]
)
= MAX ( 'Table'[Date_time] ), 0,
COUNTROWS ( 'Table' ) > 1, 1 / COUNTROWS ( 'Table' ),
1
)
)
) 
johnt75Num occurrences =
var currentClient = SELECTEDVALUE( 'Table'[idclient] )
var currentTicket = SELECTEDVALUE( 'Table'[idticket] )
var minDate = CALCULATE( MIN('Table'[Date_time]), 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket )
var result = COUNTROWS( SUMMARIZE(
FILTER( 'Table', 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket && 'Table'[Date_time] > minDate ),
'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]
) )
return resultusing SUMMARIZE across the idclient, idticket and Date_Time columns will filter out any duplicates
Hi Jihwan_Kim , the problem is that my real data has many duplicate dates like the table from the first post, with your formula they are accounted. Do you know how to remove the duplicates? thank you very much for your help.
