Forum Discussion

ezolezzi92's avatar
ezolezzi92
New Member
6 years ago

Counting by grouping

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 wou...
Anonymous's avatar
Anonymous
Not applicable
6 years ago
[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