Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count/export empty and duplicate value

Hi,

 

I have the following table: 

NameSerial numer
A2DC25F
BF3DGG
C 
D 
E5GERG
F6XDBD
G89DBBE
H6XDBD

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. 

 

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]
	)

3 Replies

  • 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. 

       

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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.