Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
26 | |
20 | |
14 | |
8 |
User | Count |
---|---|
75 | |
47 | |
45 | |
17 | |
16 |