Forum Discussion

mlim0806's avatar
mlim0806
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 Emp...
Need Help
  • PhilipTreacy's avatar
    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

     

     

PhilipTreacy's avatar
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's avatar
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's avatar
    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