Forum Discussion
SandeA
Helper III
6 years agoAdding Calculated Columns with data linked through 3 tables
I am trying to add a calculated column to Table A. The data I want (FullName) is in Table C. Each record in Table A has a PrincipalSecurityUserId on it (no null values). I need Table B to link Table ...
- 6 years ago
See if this helps. Create the two columns in Request2 as below.
PrincipalSecurityName = VAR A = Request2[PrincipalSecurityUserId] VAR B = CALCULATE(MAX(SecurityUser[UserId]),FILTER(ALL(SecurityUser),SecurityUser[SecurityUserId]=A)) RETURN CALCULATE(MAX(User[FullName]),FILTER(ALL(User),User[UserId]=B))FulfillmentSecurityName = VAR A = Request2[FulfillmentSecurityUserId] VAR B = CALCULATE(MAX(SecurityUser[UserId]),FILTER(ALL(SecurityUser),SecurityUser[SecurityUserId]=A)) RETURN CALCULATE(MAX(User[FullName]),FILTER(ALL(User),User[UserId]=B))Based on the sample data, I got this output
Let us know.
Edit:
I am assuming the path is right
User[userid]-> securityuser(userid) and securityuser(securityuserid) ->Reqest2(principalsecurityuserid)
User[userid]-> securityuser(userid) and securityuser(securityuserid) ->Reqest2(Fullfilmentsecurityuserid)
SandeA
Helper III
6 years agoVasTg Here are a couple of screenshots followed but a little bit of sample data. It's limiting how much I put in my reply so many of the table columns are deleted.
Table A to Table B
Table B to Table C
TABLE A (Request2)
| RequestNumber | PrincipalSecurityUserId | FulfillmentSecurityUserId |
| PRR1012 | 2 | 11 |
| PRR1013 | 13 | 13 |
| PRR1014 | 3 | 14 |
| PRR1022 | 13 | 13 |
| PRR1023 | 15 | 41 |
| PRR1024 | 15 | 41 |
| PRR1025 | 48 | 48 |
| PRR1026 | 13 | 42 |
| PRR1028 | 48 | |
| PRR1029 | 13 | 42 |
| PRR1030 | 13 | 13 |
TABLE B (SecurityUser)
| SecurityUserId | UserId | Department |
| 2 | 4 | Public Affairs |
| 3 | 5 | Salem - Agency Affairs |
| 13 | 26 | Salem - Agency Affairs |
| 15 | 29 | Salem - Agency Affairs |
| 48 | 73 | Human Resources |
TABLE C (User)
| UserId | FullName |
| 4 | Julie Waters |
| 5 | Bobbi Doan |
| 26 | Jim Gersbach |
| 29 | Jason Cox |
| 73 | Christy Oliver |