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.
I have a list of Customer IDs, each has multiple contact points (Contact IDs). We have our customers grouped into various segments, each with a target for the number of contact points.
Customer Contacts in the segment ABC
Customer ID | Contact ID |
1 | 100 |
1 | 103 |
1 | 109 |
2 | 123 |
2 | 138 |
3 | 150 |
That would be grouped into the following. I need the grouping to be affected by the selection of the segment slicer.
Customer ID | Contact Count |
1 | 3 |
2 | 2 |
3 | 1 |
When a slicer is filtered to segment ABC, which has the target contact count of 2, I want to count the customers that have above that target and put it in a card. The result should be 1.
I've tried using different table structures and calculations. I'm quite flexible with how the table structures can be set up and I'm open to trying different calculations as long as they can be affected by a slicer.
Thanks!
Solved! Go to Solution.
Should be something along the lines of:
Measure =
VAR __Target = MAX('TargetTable'[Target])
VAR __Table =
SUMMARIZE(
'Table',
[Customer ID],
"Count",COUNTROWS('Table')
)
RETURN
COUNTROWS(FILTER(__Table,[Count]>=__Target))
Should be something along the lines of:
Measure =
VAR __Target = MAX('TargetTable'[Target])
VAR __Table =
SUMMARIZE(
'Table',
[Customer ID],
"Count",COUNTROWS('Table')
)
RETURN
COUNTROWS(FILTER(__Table,[Count]>=__Target))
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 |
---|---|
103 | |
101 | |
87 | |
73 | |
67 |
User | Count |
---|---|
119 | |
111 | |
95 | |
79 | |
72 |