Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
[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
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!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |