Create a Measure: Count distinct values (of a field) if certain criteria of another field are met

[Anonymous]

Hi,

I've brought a table into Power BI and expanded it (in order to create an additional row for every day between two different date fields.  I needed to do this so I could count how many Members were part of a healtcare plan for any portion of the year).  I then went ahead and created my Date/Calendar table and created the relationships.  Everything is working properly:

However, I'm now trying to create an additional measure on a table visual and I'm struggling with this.  Here is my current table:

I'd like to create a New Measure called "Employee"...where it will count distinct Member IDs.  Currently, the Members column in the table visual above is counting distinct values of the 'NovaDailyEligibilityExtract'[Member ID] field.  The New Measure I'd like to create should count distinct values of the 'NovaDailyEligibilityExtract'[Member ID] field...but only if my 'NovaDailyEligibilityExtract'[Relationship] field = Employee.

What is the formula to create this New Measure?

[smpa01 · Accepted solution]

Can you please try something like

X=

CALCULATE(DISTINCTCOUNT('Table'[<column to count>]), FILTER('Table','Table'[<column to filter>]="Emp"))

[Anonymous]

That worked perfectly.  Filter is such a powerful and useful tool.