cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sukantdas Frequent Visitor
Frequent Visitor

How to count the occurrences of each value in a column, including repeated values.

Hi All,

 

I am very new to Power BI , and running with an issue. I am sure I will definitely get the solution to my issue here. 

Issue :

 

The below is a column I have

Col A

 

1

2

3

1

2

3

4

 

I need 

 

Col A          Occurance

1                   2

2                   2

3                   2

1                   2

2                   2

3                   2

4                   1

 

I want to count the occurrences of each value in a column , including the duplicates. 

 

Thanks a ton in advance.

 

Regards,

SKD

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: How to count the occurrences of each value in a column, including repeated values.

@sukantdas

Add a calculated column.

 

Occurence = 
COUNTX (
    FILTER ( yourTable, EARLIER ( yourTable[Col A] ) = yourTable[Col A] ),
    yourTable[Col A]
)

 

Capture.PNG

6 REPLIES 6
Moderator Eric_Zhang
Moderator

Re: How to count the occurrences of each value in a column, including repeated values.

@sukantdas

Add a calculated column.

 

Occurence = 
COUNTX (
    FILTER ( yourTable, EARLIER ( yourTable[Col A] ) = yourTable[Col A] ),
    yourTable[Col A]
)

 

Capture.PNG

sukantdas Frequent Visitor
Frequent Visitor

Re: How to count the occurrences of each value in a column, including repeated values.

Hi  @Eric_Zhang

 

 Thanks for the solution . It worked for me.  Can you please suggest where & how to increase knowledge in DAX.

 

Regards,

SKD

anoop_ellickal Frequent Visitor
Frequent Visitor

Re: How to count the occurrences of each value in a column, including repeated values.

I have a column like this 

 

mango

orange

mango

grape

orange

mango

orange

mango

apple

 

need an out put like 

mange 4

orange  3

Grape  1

Apple 1

Helen_R New Member
New Member

Re: How to count the occurrences of each value in a column, including repeated values.

Hi there,

 

I've jus tried using your suggested solution to count the occurence of each userID in a column and have found the output appears to square each result. For example, where a userID appears twice in the column, the output in the occurrence column is 4 and where userID appears 6 times in the column the output in the occurrence column is 36. Any idea why this is happening? I really can't get my head around it I'm afriad!

 

Thanks for any help you can provide.

nisuomi Regular Visitor
Regular Visitor

Re: How to count the occurrences of each value in a column, including repeated values.

Hello,

 

Is there any way to do this on the M Query side of things?

Been looking for a solution, but my M Query is not so strong.

 

 

 

Cheers,

Niko

rickystudds Frequent Visitor
Frequent Visitor

Re: How to count the occurrences of each value in a column, including repeated values.

THANK YOU, I have been searching all day and finnally your solution did the job!
@Eric_Zhang 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 337 members 3,433 guests
Please welcome our newest community members: