Cross information between 3 tables

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 ...

Need Help

barritown

2 years ago

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

barritown

Solution Sage

2 years ago

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