Counting distinct number of duplicate rows

[alagator28]

I have a table where I have used a DAX formula to determine if a row is a "duplicate" by counting the number of occurrences. In this scenario, a duplicate is when the values of Fruit and Color in one row both match the values for Fruit and Color in another row.

Occurences =

VAR _CurrentFruit = 'Table1'[Fruit]

VAR _CurrentColor = 'Table1'[Color]

RETURN

    COUNTROWS(

        FILTER(

            'Table1',

            'Table1'[Fruit] = _CurrentFruit &&

             'Table1'[Color] = _CurrentColor

        )

    )

This works and tells me if it's a duplicate (any value over 1) and if a duplicate, how many duplicates of that same row exist.

Table1

Fruit	Color	Occurences
Apple	Red	1
Apple	Green	3
Grape	Purple	2
Apple	Green	3
Grape	Green	1
Apple	Green	3
Grape	Yellow	1
Apple	Yellow	1
Grape	Purple	2

However, I would now like to know how many distinct duplicates there are. In this case there are 2 distinct repeating rows. How can I calculate this?

Fruit	Color	Occurrences
Apple	Green	3
Grape	Purple	2

Thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new table.

Expected table = 
	SUMMARIZE(
		FILTER(
			ADDCOLUMNS(
				Table1,
				"@occurences", VAR _CurrentFruit = 'Table1'[Fruit]
				VAR _CurrentColor = 'Table1'[Color]
				RETURN
					COUNTROWS(FILTER(
						'Table1',
						'Table1'[Fruit] = _CurrentFruit &&
						'Table1'[Color] = _CurrentColor
					)
					)
			),
			[@occurences] > 1
		),
		Table1[Fruit],
		Table1[Color],
		[@occurences]
	)

[alagator28]

Thank you @Jihwan_Kim ! It seems to provide the correct answer for me, but I want to be sure I applied it as you intended. I created a new measure and used this to get "2" as my value. As it's my first time using "Summarize", it's new to me. It's interesting to see that I can generate a virtual table, I didn't know about this functionality.

# Distinct Duplicates = 

VAR _Table =	SUMMARIZE(
		FILTER(
			ADDCOLUMNS(
				Table1,
				"@occurences", VAR _CurrentFruit = 'Table1'[Fruit]
				VAR _CurrentColor = 'Table1'[Color]
				RETURN
					COUNTROWS(FILTER(
						'Table1',
						'Table1'[Fruit] = _CurrentFruit &&
						'Table1'[Color] = _CurrentColor
					)
					)
			),
			[@occurences] > 1
		),
		Table1[Fruit],
		Table1[Color],
		[@occurences]
	)

RETURN COUNTROWS(_Table)