Cross information between 3 tables

[smccres]

In power BI desktop I am looking forward to have a "Training Matrix" table with Can Execute / Cannot Execute certification status, as below:

UserName		Operation		Certification Status
User_A		[01] Cleaning
User_A		[02] Painting
User_A		[03] Moving
User_A		[04] Closing
User_B		[01] Cleaning
User_B		[02] Painting
User_B		[03] Moving
User_B		[04] Closing
User_C		[01] Cleaning
User_C		[02] Painting
User_C		[03] Moving
User_C		[04] Closing

Than I have 2 other tables with related information: A table with the certifications required for each operation (OperationCertRequirement) and a table with certification records (UserCertification)

OperationCertRequirement

id		Operation		Certificate_Needed
1		[01] Cleaning		Clean with X
2		[01] Cleaning		Clean with Y
3		[01] Cleaning		How to Inspect
4		[02] Painting		Paint with Paint
5		[02] Painting		Paint with Ink
6		[03] Moving		Move Vertically
7		[03] Moving		Move Horizontally
8		[04] Closing		How to Close

UserCertification

id_cert		Certificate		User		Status
1		Clean with X		User_A		Active
2		Clean with Y		User_A		Active
3		How to Inspect		User_A		Active
4		Move Vertically		User_A		Active
5		How to Close		User_A		Active
6		Clean with Y		User_A		Expired
7		Paint with Paint		User_B		Active
8		Paint with Ink		User_B		Active
9		Move Vertically		User_B		Active
10		Paint with Paint		User_B		Active
11		How to Close		User_C		Active
12		Clean with X		User_C		Expired
13		Clean with Y		User_C		Expired
14		How to Inspect		User_C		Expired

To fill the empty column, I need to see if the operator has the required certificates active for the related operation.

Because some operations may require more than one certification, I'm struggling to find a solution to fill the certification status comulm, which should be as follows:

UserName		Operation		Certification Status
User_A		[01] Cleaning		Can Execute
User_A		[02] Painting		Cannot Execute
User_A		[03] Moving		Cannot Execute
User_A		[04] Closing		Can Execute
User_B		[01] Cleaning		Cannot Execute
User_B		[02] Painting		Can Execute
User_B		[03] Moving		Cannot Execute
User_B		[04] Closing		Cannot Execute
User_C		[01] Cleaning		Cannot Execute
User_C		[02] Painting		Cannot Execute
User_C		[03] Moving		Cannot Execute
User_C		[04] Closing		Can Execute

Thank you very much in advance for any help!

Edit: Tables style edited for clarification.

[barritown · Accepted solution]

Hi @smccres,

Assuming that all three tables are not related, I could propose such a calculated column:

And in plain text:

Certification Status = 
VAR _curtUser = [UserName]
VAR _curOperation = [Operation]
VAR _tbl1 = SELECTCOLUMNS ( FILTER ( OperationCertRequirement, [Operation] = _curOperation ), "Value1", [Certificate_Needed] )
VAR _tbl2 = SELECTCOLUMNS ( FILTER ( UserCertification, [User] = _curtUser && [Status] = "Active" ), "Value2", [Certificate] )
VAR _res = ADDCOLUMNS ( _tbl1, "Flag", INT ( NOT [Value1] IN _tbl2 ) )
RETURN IF ( SUMX ( _res, [Flag] ) > 0, "Cannot Execute", "Can Execute" )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[smccres]

Hi barritown,

Thank you very much for your replay, that is a clever way to do it.

However it is not fully functioning as I do have active relationships. Is there any way to deactivate them for this calculated column? If crossfilter-none could be used, where should it be placed?

Thanks 

[barritown]

I can assume that if you use ALL(OperationCertRequirement) and ALL(UserCertification) instead of just OperationCertRequirement and UserCertification in the 4th and 5th lines, it may work properly even with the relations, but it's only an assumption.

You can also provide me a mock file with the model like you use in the production environment so I can adjust this solution for your model.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[smccres]

Hi Alexander,

I found an error in a field name and so your 1st solution worked perfectly!

Thank you very much again.