Identify Employees with Multiple Certificates using disconnected table slicer

[Richard_Halsall]

Hi

My data is structured as follows

Employee

id	firstname	lastname
1234	abc	defg
5678	hijk	lmno

Certificate Group

Group Name	Certificate Name
GroupA	CertA
GroupA	CertB
GroupA	CertC
GroupB	CertC
GroupB	CertD
GroupB	CertF
GroupB	CertZ

Certificates Held

Employeeid	certificateName
1234	CertA
1234	CertB
1234	CertC
1234	CertF
1234	CertZ
5678	CertA
5678	CertB
5678	CertC
5678	CertD
5678	CertF
5678	CertZ

I need to select a certificate group, prefereably using a disconnected table slicer, and have a table visual returned of all employees who have ALL the certificates specified in the Certificate Group Table

So in the above example selecting

Certificate Group 'GroupA' would show employees 1234 and 5678

Certificate Group 'GroupB' would show employee 5678

Any advice would be appreciated. Thanks

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @amitchandak  provided, your soution is excellent, and i want to offer some more information for user to refer to.

hello @Richard_Halsall , based on your description, you can create a relatiohip between your emplyee table and your Certificates Held table.

Then you can create a meaure.

Flag =
VAR a =
    VALUES ( 'Certificate Group'[Certificate Name] )
VAR b =
    CALCULATE (
        COUNTA ( 'Certificates Held'[Employeeid] ),
        'Certificates Held'[certificateName] IN a
    )
RETURN
    IF (
        ISFILTERED ( 'Certificate Group'[Group Name] ),
        IF ( COUNTROWS ( a ) = b, 1 ),
        1
    )

Then put the measure and id of employee table to the table visual.

Output

Best Regards!

Yolo Zhu

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

[Richard_Halsall]

Many thanks worked perfectly

[Anonymous]

Hi,

Thanks for the solution @amitchandak  provided, your soution is excellent, and i want to offer some more information for user to refer to.

hello @Richard_Halsall , based on your description, you can create a relatiohip between youe emplyee table and your Certificates Held table.

Then you can create a meaure.

Flag =
VAR a =
    VALUES ( 'Certificate Group'[Certificate Name] )
VAR b =
    CALCULATE (
        COUNTA ( 'Certificates Held'[Employeeid] ),
        'Certificates Held'[certificateName] IN a
    )
RETURN
    IF (
        ISFILTERED ( 'Certificate Group'[Group Name] ),
        IF ( COUNTROWS ( a ) = b, 1 ),
        1
    )

Then put the measure and id of employee table to the table visual.

Output

Best Regards!

Yolo Zhu

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

[amitchandak]

@Richard_Halsall , Table 2 and three should be disconnected

measure =
var _tab = Values('Certificate Group'[Certificate Name])
var _cnt = countrows(_tab)
return
Countx( summarize(Filter('Certificates Held', Certificates Held[certificateName] in _tab), 'Certificates Held'[Employeeid], "_actual", distinctcount( Certificates Held[certificateName])), if([_actual] =_cnt, [Employeeid], blank()))

plot this with employee id of table 3 or with 1(If joined with 3)

[Richard_Halsall]

@amitchandak Hi, thanks for the swift repsonse the measure you have provided does work but when used on the table 'Certificates Held'  approx. 46k records I am experiencing performance issues - is there anyway to optimise. Thanks