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
raellaylo
Frequent Visitor

Calculated column to get number of occurrences is returning values that squares the correct count

Hello.. I have been searching all day for an answer to my dilemma.  I wanted to get the count of how many times the value in a column appears in the column. I have found lots of solutions involving COUNTROW(FILTER formula that also involves EARLIER function. I am amazed that it has worked for a lot of the users who needed that solution.  But for some reason, it does not work for me.  The resulting value squares the correct count. 

 

To illustrate, for example I have below data.  Table name is "Data".  I want column B to return the count of occurrence of the ID in column A:
sample.JPG

 

So I made this formula in Power BI Desktop via a calculated column.  As you can see, the result is squaring the actual count:

result.JPG

 

I hope someone can help me with this.  Any possible reason why this is happening? And any solution for this?

 

Thanks in advance. 

 

1 ACCEPTED SOLUTION
BarnabasToth
Resolver I
Resolver I

It seems the implicit aggregation for the value was left on Sum.

Here are two approaches to address the issue with a sample file.

Regards

View solution in original post

2 REPLIES 2
BarnabasToth
Resolver I
Resolver I

It seems the implicit aggregation for the value was left on Sum.

Here are two approaches to address the issue with a sample file.

Regards

amitchandak
Super User
Super User

@raellaylo , Create a measure

countrows(filter(allselected(Table), Table[ID] =max(Table[ID]))

 

I think simple measure

countrows(Table)

 

should also work

Helpful resources

Announcements
Europe Fabric Conference

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.