Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count/export empty and duplicate value

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 ha...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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]
	)
johnt75's avatar
johnt75
Super User
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

Thank you for your advice.