Forum Discussion

Frequent Visitor

3 years ago

Solved

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!

PhilipTreacy
3 years ago
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?

Download my example PBIX file

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

PhilipTreacy
Super User
3 years ago
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?

Download my example PBIX file

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
- mlim0806
  Frequent Visitor
  3 years ago
  Thank you! It was an instance where Employee ID's could be hired on multiple accounts, so I wanted to count distinct. This worked!
  - PhilipTreacy
    Super User
    3 years ago
    Hi mlim0806
    
    Glad this worked. Please mark my answer as the solution so that anyone else reading this knows the solution.
    
    Regards
    
    Phil