Forum Discussion
If then statement using distinct counts
Hello,
I have a list of employee IDs that are each assigned a Hire flag of 'yes' or 'no'. I am trying to create a new measure that says.
IF Hire field equals 'yes' THEN distinct count of Employee IDs
I would like this displayed as a whole number. Appreciate the help!
Hi mlim0806
Presumably the Employee ID's are unique? And therefore a 'distinct' count is just a count as the ID's are already distinct?
Try this
Measure = CALCULATE(COUNTROWS('DataTable'), FILTER('DataTable', 'DataTable'[Hire] = "Yes"))But if ID's are repeated, you can get your Distinct Count with this measure
Distinct ID Count = CALCULATE(DISTINCTCOUNT('DataTable_2'[ID]), FILTER('DataTable_2', 'DataTable_2'[Hire]="yes"))regards
Phil
3 Replies
Hi mlim0806
Presumably the Employee ID's are unique? And therefore a 'distinct' count is just a count as the ID's are already distinct?
Try this
Measure = CALCULATE(COUNTROWS('DataTable'), FILTER('DataTable', 'DataTable'[Hire] = "Yes"))But if ID's are repeated, you can get your Distinct Count with this measure
Distinct ID Count = CALCULATE(DISTINCTCOUNT('DataTable_2'[ID]), FILTER('DataTable_2', 'DataTable_2'[Hire]="yes"))regards
Phil
Thank you! It was an instance where Employee ID's could be hired on multiple accounts, so I wanted to count distinct. This worked!
Hi mlim0806
Glad this worked. Please mark my answer as the solution so that anyone else reading this knows the solution.
Regards
Phil
