Measure: count duplicates.

[jelsms]

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.

[ibarrau]

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,

[jelsms]

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?

[ibarrau · Accepted solution]

Ok, now we are talking  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.

[jelsms]

Thank you. It works.