cancel
Showing results 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

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

7 REPLIES 7
Helper V

Hi,

Is there a way this can be solved please?

thanks.

Resolver II

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

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.

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

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

Super User

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

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors