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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vnqt
Helper IV
Helper IV

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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