Calculated Column to check if a person has two specific roles for a given project

[sjrrkb123]

Suppose I Have three columns, Name, Role, & Project that look like this

Project	Role	Name
A	Owner	Shane
A	Helper	Shane
A	Seller	Bill
B	Owner	Shane
B	Help	Eric
B	Seller	Bill
C	Owner	Jenna
C	Owner	Eric
C	Help	Bob
C	Seller	Eric

I want to add a calculated column that states if a person has the role of Owner and Helper,  it has a "Yes" else it is Blank. I person can more than 1 role in a project but I only care if they have both roles of Owner and Helper. So it would look like the following:

Project	Role	Name	Dup Role Check
A	Owner	Shane	Yes
A	Helper	Shane	Yes
A	Seller	Bill
B	Owner	Shane
B	Help	Eric
B	Seller	Bill
C	Owner	Jenna
C	Owner	Eric
C	Help	Bob
C	Seller	Eric

How would I go about doing this?

[camargos88]

Hi @sjrrkb123 ,

Try this measure:

Check = IF(CALCULATE(DISTINCTCOUNT('Table'[Role]); 'Table'[Role] in {"Owner";"Helper"}; ALLEXCEPT('Table';'Table'[Name];'Table'[Project])) = 2;"Yes"; BLANK())

Ricardo

[az38 · Accepted solution]

Hi @sjrrkb123 

create a measure like

Dup Role Check = 
var _isRoleOwner = IF(CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Name], Table[Project]), Table[Role]="Owner") > 0, TRUE(), FALSE() )
var _isRoleHelper = IF(CALCULATE(COUNTROWS(Table), ALLEXCEPT(Table, Table[Name], Table[Project]), Table[Role]="Helper") > 0, TRUE(), FALSE() )

RETURN

IF(_isRoleOwner && _isRoleHelper, "Yes", BLANK())