Count/export empty and duplicate value

[Anonymous]

Hi,

I have the following table: 

Name	Serial numer
A	2DC25F
B	F3DGG
C
D
E	5GERG
F	6XDBD
G	89DBBE
H	6XDBD

Could you please help to count Names which have duplicate and empty Serial Number 

The result would be 4  here. I would like to display the result in a card and can export these names.

Thank you in advance. 

[v-jingzhang]

Hi @Anonymous 

You can use this measure to count

Count Measure = SUMX(FILTER(SUMMARIZE('Table','Table'[Serial numer],"Count",COUNT('Table'[Name])),[Count]>1),[Count])

If you want to display the names in the report, you can use another measure as a filter. Add Name column to a table visual in the report, then add below measure to filter pane as a visual-level filter on this table visual. Set it to show items when value is 1. 

Filter Flag = 
var vSNs = SELECTCOLUMNS(FILTER(SUMMARIZE(ALL('Table'),'Table'[Serial numer],"Count",COUNT('Table'[Name])),[Count]>1),"SN",[Serial numer])
return
IF(SELECTEDVALUE('Table'[Serial numer]) IN vSNs, 1, 0)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[johnt75 · Accepted solution]

You can create a measure like

Duplicates and blanks =
VAR SummaryTable =
	ADDCOLUMNS(
		SUMMARIZE( 'Table', 'Table'[Serial numer] ),
		"@num rows", CALCULATE( COUNTROWS( 'Table' ) )
	)
RETURN
	SUMX(
		FILTER(
			SummaryTable,
			[@num rows] > 1 || ISBLANK( 'Table'[Serial numer] )
		),
		[@num rows]
	)

[Anonymous]

Thank you for your advice.