Forum Discussion

PBIfanatic's avatar
PBIfanatic
Icon for Helper V rankHelper V
4 years ago

Count first occurance

Hi,

I have a data requirement to count only the first occurance of an event.

So if a Sales rep contacts a company, only the first contact is to be counted.

 

Sales RepCompanyMonth
ABCCompany AJan
ABSCompany BJan
ABCCompany AFeb

 

In this example, the companies the sales rep has contacted is 2. (Company A and Company B)

When I drag in month, I get 2 for Jan, 1 for Feb. which is correct but 

I dont need to count the company if it has been counted before. 

 

thanks 

 

 

 

 

 

Need Help

7 Replies

  • Kishore_Kadhir's avatar
    Kishore_Kadhir
    Icon for Resolver II rankResolver II
    4 years ago

    Hi PBIfanatic ,

     

    Can you create a measure like this and see if it is working?

    Total =
    VAR firstcompany = FIRSTNONBLANK('Table'[Company], MIN('Table'[Company]))
    RETURN
    CALCULATE(COUNTROWS('Table'), 'Table'[Company]=firstcompany)

    Regards,
    Kishore
  • PBIfanatic's avatar
    PBIfanatic
    Icon for Helper V rankHelper V
    4 years ago

    Hi, I have used Calculate(DistinctCount(Table[Company]), Filter(Sales, [#SalesContact] > 0) this works without the month field. When I use the month field is when the company is counted for each month.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

      Try this,

      Calculate(DistinctCount(Table[Company]), Sales, [#SalesContact] > 0,Removefilters(ales, [#Month] )