Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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)
@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 (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 |
@VasTg - I also want to add that I did NOT design or create these tables. I pull it from an existing DB and the original developer, for some reason, created multiple tables that easily could have been combined.
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.
@VasTg - I tried to move things around to make the data model easier to see the lines. I also deleted a couple of tables from the model that I realized I don't need. But I don't think it changed any of the active relationships.
I went thru the datamodel and relationships. For you to bring in the User name from User(Table C) dimension to Request2(Table A), there is already a direct relationship exist. Simple you could create a calculated column as below. But it is not necessary to build a visual.
UserName = Related(User[FullName])
Let us know if it didn't work.
Well, it would work if I just had one user ID to link it to. The UserId in Request2 is not the name I'm trying to pull. I have 2 different columns to make, one for the PrincipalSecurityUserID and another for the FulfillmentSecurityUserId (both of these columns are in Request2/TableA). These columns contain the same values as the SecurityUserID (found in SecurityUser/Table B) that will then link to User/Table C to pull the name associated with the PrincipalSecurityUserId or the FulfillmentSecurityUserId. Does that make more sense?
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)
YES!! Thank you so much for all your help! This did the trick! I completely forgot about using the VAR options!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |