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 understand, you want the overall total not broken down by client / ticket.

Num occurrences = SUMX( ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[idclient], 'Table'[idticket], 'Table'[Date_time] ),
"@value",
var currentClient = CALCULATE( SELECTEDVALUE( 'Table'[idclient] ) )
var currentTicket = CALCULATE( 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 result 
), [@value])

should give you what you're after

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thank you again for your answer! There is another problem that I forgot to mention. When you select a filter like for instance month, in your formula the first result is not considered even it is the same occurrence from the previous month. 
About your new formula, the result for idclient 001 is 55 and should be 10 and If you filter august should be 5.  PBi file https://1drv.ms/u/s!AuMLcKZkL7PFgkY3qjc2GJ35giM2?e=MAcPtQ
thank you again for your time.

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

    Can you confirm what the date filter is supposed to filter out. You say that August should return 5, so it looks like you want to find the first occurence of a ticket / client combination regardless of when it occurred and then find the number of repeat occurences within the selected time frame ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      yes, that's right. I need to find repeated occurrences for the same idclient and ticket excluding the duplicate values)

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

        Try

        Num occurrences = 
        SUMX(
        ADDCOLUMNS(
        SUMMARIZE( 'Table', 'Table'[idclient], 'Table'[idticket] ),
        "num instances",
        var firstInstance = CALCULATE( MIN( 'Table'[Date_time] ), REMOVEFILTERS( 'Date' ) )
        RETURN CALCULATE( COUNTROWS( SUMMARIZE( 
        FILTER('Table', 'Table'[Date_time] > firstInstance ),
        'Table'[idclient], 'Table'[idticket], 'Table'[Date_time]
        ) ) )
        ),
        [num instances]
        )