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
Num 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 result

using SUMMARIZE across the idclient, idticket and Date_Time columns will filter out any duplicates

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi johnt75 ,

thank you very much answer but the measure result is blank, did you tested?

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

    Yes, if I put that measure on a table or matrix visual with idticket and idclient on the rows then I get correct values.

    Do you have any other filters applied which could restrict the results ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      No, I don't have any filters that restrict the results. I need this measure on a card but the result of that measure is blank. In the matrix the total that you see is blank and for that reason on the card is blank too.

      In the future, I will only have a filter that is in the date table "interval" to see which tickets belong to each interval.

      Your solution seems so elegant and nice but for the moment doesn't work for this purpose :/. 

      • 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