Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ...
Need Help
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

I think you could add a calculated column as below, then filter out any entries which are 1

Is first entry = 
var currentTicket = 'Table'[idticket]
var currentClient = 'Table'[idclient]
var currentDateTime = 'Table'[Date_time]
var numPrevEntries = CALCULATE( COUNTROWS('Table'), REMOVEFILTERS(), 'Table'[idclient] = currentClient && 'Table'[idticket] = currentTicket 
&& 'Table'[Date_time] < currentDateTime )
return IF ( numPrevEntries = 0, 1, 0)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi johnt75 actually as a column does not work because there is no row context I guess but if I write this way almost work. The only problem is that the duplicates are also 1 in this formula:

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

Thank you! I am learning a lot with you 🙂
  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    I meant for you to use the calculated column so that you could display the data in a table, not to replace the num occurences measure.

    By adding the new column as a filter the table will also obey any other filters or slicers you apply. e.g. using a slicer to select August produces

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I know johnt75 , I didn't replace it. I used it as a filter. What I was trying to do is display the total for each client and the total for all in a table. That's why I said that if you tried to do with the calculated column the duplicates appear because they also have 1 in the condition.

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    You may be able to get rid of the duplicates if you replace the ALL('Table') with

    CALCULATETABLE( SUMMARIZE( 'Table', 'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]),
        REMOVEFILTERS( 'Table' )
    )