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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
jelsms
Frequent Visitor

Measure: count duplicates.

Hello Everyone! I am sending this post to see if someone can help me with this:

I have a table like this:

Account       Status

1                  Approved

2                  Approved

1                  Returned

1                  Rejected 

3                  Pending approve

4                  Approved

5                  Approved

5                  Returned

6                  Pending approve

7                  Approved

7                  Returned

 

I have been trying to get a measure to count duplicates:

Account   Status

1                Approved

1                Returned

1                Rejected 

5                Approved

5                Returned

7                Approved

7                Returned

 

Table:

Account   Status

1                  3

5                  2

7                  2

 

The result expected is:

Total: 3 accounts duplicated, 7 times in total. I need a measure for that. I found a lot to count disting but not count duplicates.

 

Any advice?

 

Thank you.

Jose.

1 ACCEPTED SOLUTION

Ok, now we are talking Smiley Happy my suggestion was only going to work for a table.

Let's try this in order to know how much accounts have duplicates.

Create a Measure:

COUNTOROWS(
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( table, table[account] ),
            "Duplicates", CALCULATE ( COUNTROWS ( table ) )
        ),
        [Duplicates] > 1
    )
)

Hope this works to build the card,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

4 REPLIES 4
ibarrau
Super User
Super User

Hi. Can you explain a bit more about your objetive here?

I'm asking because building this is simple:

 

Table:

Account   Status

1                  3

5                  2

7                  2

 

Just create a table with Account  (don't summarize) and then any field of the same table with (count aggregation)

There you have every account and the rows involving it. Then you can filter the table by count > 1 on visual level filter.

 

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hello and thank you for your answer. I want to have a card visualization of the number of account that have duplicates. I do not want more tables. i tried summarize, group by and then filter > 1 but it does not worked. Also I have a matrix table that shows the accounts and more information. Any help for the measure?

Ok, now we are talking Smiley Happy my suggestion was only going to work for a table.

Let's try this in order to know how much accounts have duplicates.

Create a Measure:

COUNTOROWS(
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( table, table[account] ),
            "Duplicates", CALCULATE ( COUNTROWS ( table ) )
        ),
        [Duplicates] > 1
    )
)

Hope this works to build the card,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Thank you. It works.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.