Forum Discussion
Adding 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 A & Table C. I've check the data model and from what I can tell, they are linked there just fine (not always actively linked, but with the dotted line). I've tried multiple filters and functions with no luck (I'm still fairly new at Power BI). Any help would be greatly appreciated!
Below is how the tables are linked.
Table A:
PrincipalSecurityUserId
Table B:
SecurityUserId
UserId
(Tables A & B are linked with SecurityUserID-different names but the same value)
Table C:
UserId
FullName
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)
10 Replies
VasTg 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 I somehow missed your post shuffling around tabs in Chrome.Apologies for the delay.
Its really hard to understand from the picture. Somewhere it makes a circular reference when you try to define the relationship.
Could you post the table structure format of all the relationships with column names and filter direction format?
Table1,join_column in table1, Table2, join_column in table2,filter direction
If you go to manage relationships in model, it will give the info on first four.
Edit: Just the active ones. If it is not sensitive data, please attach the PBIT file.
