Forum Discussion
Cross information between 3 tables
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
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
Solution SageI 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
Hi Alexander,
I found an error in a field name and so your 1st solution worked perfectly!
Thank you very much again.