Calculated Column to check if Id column is assigned with all the values in second column

[Anonymous]

Hi, I am trying to create  preferably calculated column( If not possible then a measure) for the purpose as explained below. Here there are several Team IDs in ‘Column A’ and Roles in ‘Column B’. The condition to be checked is if each ID of first column is assigned with all the Employee Role’ in the entire table (We have 55 ID’s and more than 54 Employee Roles) then output should be true. That is if  Team ID ‘1’ is assigned with all the roles mentioned in ‘Employee Role’ column then output should be true else false.

Thank you,

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Test =
var _column=
CALCULATE(DISTINCTCOUNT('Table'[Employee Role]),'Table')
var _groupcount=
CALCULATE(DISTINCTCOUNT('Table'[Employee Role]),FILTER(ALL('Table'),'Table'[ID]=EARLIER('Table'[ID])))
return
IF(
    _column=_groupcount,TRUE(),FALSE())

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Thank you very much @liu Yang

This solution is perfect for the question I posted but  also the requirement is to get the list of missing roles for each Id and the count of missing roles when each ID is selected. Can you please suggest how this can be acheieved.