Count first occurance

[PBIfanatic]

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 Rep	Company	Month
ABC	Company A	Jan
ABS	Company B	Jan
ABC	Company A	Feb

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 

[PBIfanatic]

Hi,

Is there a way this can be solved please?

thanks.

[Kishore_Kadhir]

Can you post a screenshot with expected result?

Regards,

Kishore

[Anonymous]

Hi,

Try this,

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

[PBIfanatic]

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.

[Kishore_Kadhir]

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]

Hi thanks for your reply @Kishore_Kadhir 

Tried it but it didnt work, values came as 1 for each month.

[ribisht17]

@PBIfanatic 

Step 1 = CALCULATE(min('Table'[Date]),ALLEXCEPT('Table','Table'[Ccompany],'Table'[Sales Rep]))

Final Sol = if('Table'[Step 1]='Table'[Date],1,0)

So above is not counting when ABC contacted Company A for the second time

I have also made one video on the ALLEXCEPT concept here https://youtu.be/WCMwCj29TTU

Regards,

Ritesh

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users