cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vnqt
Helper III
Helper III

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. 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @vnqt 

 

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)

vjingzhang_0-1674725207406.png

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

johnt75
Super User
Super User

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

Thank you for your advice. 

 

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors