Power Bi calculated column to do the lookup

[biswad]

I have a table called "Branch" and another table called "Role" as per the screenshot and I want to have the output as mentioned in my output screenshot. I want to add a calculated column in the Branch table as "Status" where it will check the branch and the role against it in the "Role" table and check if the role " Doctor, Nurse, Plumber) are available under that branch in branch table or not. Basically, I would like to get an output in an additional column in-branch table where it will check each role and if it finds a branch in the role table that has all the three roles it will update the status in the branch table against that branch as a status "Full", if any one of the roles found then"1/3 Partial" if any 2 roles found than update "2/3 Partial" if non of the role found or if any branch which is not in the role table it will update the status against that branch and the missing branch as "Dorment". How can I achieve this, any help on this would be greatly appreciated.

Branch Table

Branch

Cambridgeshire

Norfolk

Suffolk

Peterborough

Central Bedfordshire

Milton Keynes

Role Table
Branch	Role
Cambridgeshire	Doctor
Cambridgeshire	Nurse
Norfolk	Doctor
Norfolk	Nurse
Norfolk	Plumber
Suffolk	Plumber
Peterborough	Driver

Output
Branch	Status
Cambridgeshire	2/3 Partial
Cambridgeshire	2/3 Partial
Norfolk	Full
Suffolk	Dorment
Peterborough	Dorment
Central Bedfordshire	Dorment
Milton Keynes	Dorment

[lbendlin]

Link both tables via the Branch field

Filter the Role table to exclude all other roles

Create a table visual with the branch field from the branch table

enable "Show items with no data"

create a measure that converts the distinct count of roles into words

Measure = SWITCH(DISTINCTCOUNT(Role),3,"Full",2,"2/3 Partial",1,"1/3 Partial","Dormant")