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 StatusUser_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) OperationCertRequirementid Operation Certificate_Needed1 [01] Cleaning Clean with X2 [01] Cleaning Clean with Y3 [01] Cleaning How to Inspect4 [02] Painting Paint with Paint5 [02] Painting Paint with Ink6 [03] Moving Move Vertically7 [03] Moving Move Horizontally8 [04] Closing How to Close UserCertificationid_cert Certificate User Status1 Clean with X User_A Active2 Clean with Y User_A Active3 How to Inspect User_A Active4 Move Vertically User_A Active5 How to Close User_A Active6 Clean with Y User_A Expired7 Paint with Paint User_B Active8 Paint with Ink User_B Active9 Move Vertically User_B Active10 Paint with Paint User_B Active11 How to Close User_C Active12 Clean with X User_C Expired13 Clean with Y User_C Expired14 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 StatusUser_A [01] Cleaning Can ExecuteUser_A [02] Painting Cannot ExecuteUser_A [03] Moving Cannot ExecuteUser_A [04] Closing Can ExecuteUser_B [01] Cleaning Cannot ExecuteUser_B [02] Painting Can ExecuteUser_B [03] Moving Cannot ExecuteUser_B [04] Closing Cannot ExecuteUser_C [01] Cleaning Cannot ExecuteUser_C [02] Painting Cannot ExecuteUser_C [03] Moving Cannot ExecuteUser_C [04] Closing Can Execute Thank you very much in advance for any help! Edit: Tables style edited for clarification.

Frequent Visitor

2 years ago

Solved

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

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

4 Replies

barritown
Solution Sage
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
- smccres
  Frequent Visitor
  2 years ago
  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
    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

Forum Discussion

Cross information between 3 tables

4 Replies

Related content

Rest API Informations

Missing information

Information button

Need information about capacity usage

Fetch licences information