Reply
Frequent Visitor
Posts: 6
Registered: ‎01-01-2017
Accepted Solution

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


Accepted Solutions
Moderator
Posts: 3,051
Registered: ‎03-06-2016

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

View solution in original post


All Replies
Moderator
Posts: 3,051
Registered: ‎03-06-2016

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

Frequent Visitor
Posts: 6
Registered: ‎01-01-2017

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

Frequent Visitor
Posts: 3
Registered: ‎06-07-2018

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

New Member
Posts: 1
Registered: ‎08-03-2018

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.

Highlighted
Regular Visitor
Posts: 43
Registered: ‎12-09-2016

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