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

      • 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.