Count based on status for unique values

Question & Answer
,
1

Hi, I’m trying to count the number of opportunities that have a task with at least one “complete” status, but I don’t want to count it twice if there are two tasks for that opportunity with “complete” status. Then I am grouping by user assigned to the task in a table visual to see how many unique opportunities they have completed a task for. I’m struggling to figure out how to get my calculated field to only count each opportunity once when the user has multiple complete tasks.

My data looks something like this:

  1. Task1, Opp1, User1, complete
  2. Task2, Opp2, User1, incomplete
  3. Task3, Opp1, User1, complete
  4. Task4, Opp3, User2, incomplete
  5. Task5, Opp4, User2, complete
  6. Task6, Opp4, User2, incomplete
  7. Task7, Opp5, User1, complete
  8. Task8, Opp6, User2, incomplete

Then when I show the data in a table per user, I would want to my calculated field to show:

User1 = 2
User 2 = 1

Because that’s the number of unique opportunities they have at least one completed task for. Any suggestions on how to accomplish this?

2

Hi @Josh_H,

These 2 calculated fields should give you what you’re looking for.

Completed tasks by opportunity:

distinctCountOver(
	ifelse(status = 'complete', task, null),
	[opportunity],
	PRE_AGG
)

Opportunities with at least 1 completed task by user:

distinct_countIf(Opportunity, {Completed tasks by opportunity} >= 1)
2 Likes
3

Welcome back, @David_Wong! Hope you had a great vacation! :slight_smile:

1 Like