Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PBIfanatic
Helper V
Helper V

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 

 

 

 

 

 

7 REPLIES 7
PBIfanatic
Helper V
Helper V

Hi,

 

Is there a way this can be solved please?

 

thanks.

Can you post a screenshot with expected result?

 

Regards,

Kishore

Anonymous
Not applicable

Hi,

 

Try this,

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

PBIfanatic
Helper V
Helper V

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

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

Hi thanks for your reply @Kishore_Kadhir 

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

@PBIfanatic 

 

 

Step 1 = CALCULATE(min('Table'[Date]),ALLEXCEPT('Table','Table'[Ccompany],'Table'[Sales Rep]))
 
Final Sol = if('Table'[Step 1]='Table'[Date],1,0)
 
ribisht17_0-1657367336898.png

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 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.