Counting by grouping

[ezolezzi92]

Hi all,

I'm trying to come up with a formula that counts & shows the departments for each of the following IDs:

ID	Department
101290	A
103458	B
101290	B

For example, I would like to show for 101290: 2 (A & B)

Could someone please guide me?
Thanks in advance.

[Anonymous]

[Departments] =
// HASONEFILTER makes sure that you have put
// a filter on ID and only one ID is visible
// in the current context. You can also use
// HASONEVALUE but this one only makes sure
// that one value of ID is visible in the current
// context, be it through an explicit filter
// or cross-filtering.
IF( HASONEFILTER( T[ID] ),

	var __depts = VALUES( T[Department] )
	var __deptCount = COUNTROWS( __depts )
	var __output =
		if( __deptCount > 0,
			__deptCount & " (" &
			& CONCATENATEX(
				__depts,
				T[Department],
				",",
				T[Department],
				ASC)
			& ")"
		)
	return
		__output
)

Best

D

[harshnathani]

Hi @ezolezzi92 ,

You can create 2 measures

Departments = CONCATENATEX(FILTER(ALL('Table'), 'Table'[ID] = MAX('Table'[ID])),'Table'[Department],",")

Count of ID = COUNT('Table'[ID])

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!