Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
vnqt
Helper V
Helper V

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.