Count Only Once for Duplicate Values Caused due to Date

[jnnilesh29]

Hello everyone, 

My table has unique activity ID and each activity ID has an activity status (Not Started/In Progress/Completed) associated with it. 

I would like to calculate a total number of activities with Not Started or In Progress or Completed status. 

However these activities IDs are duplicated becasue of unpivot column action to date column and therefore I'm getting wrong results.

I tried formula below but still not getting right results 

Not Started Count = CALCULATE(DISTINCTCOUNT('Activity Code with Budgeted Units'[Activity ID]),'Activity Code with Budgeted Units'[Activity Status]="Not Started")

Can somebody help me with this? Your help is appreciated.

Regards,

Nilesh Jain

[az38 · Accepted solution]

Hi @jnnilesh29 

try ALL

Not Started Count = CALCULATE(DISTINCTCOUNT('Activity Code with Budgeted Units'[Activity ID]),'Activity Code with Budgeted Units'[Activity Status]="Not Started", ALL('Activity Code with Budgeted Units'))

or show please what exactly wrong with your result

[jnnilesh29]

Thanks, it did work.