Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SandeA
Helper III
Helper III

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

1 ACCEPTED SOLUTION
VasTg
Memorable Member
Memorable Member

@SandeA 

 

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

image.png

 

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)

Connect on LinkedIn

View solution in original post

10 REPLIES 10
SandeA
Helper III
Helper III

@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 BTable A to Table B

Table B to Table CTable B to Table C

 

 

 

 

 

 

 

 

TABLE A (Request2)

RequestNumberPrincipalSecurityUserIdFulfillmentSecurityUserId
PRR1012211
PRR10131313
PRR1014314
PRR10221313
PRR10231541
PRR10241541
PRR10254848
PRR10261342
PRR102848 
PRR10291342
PRR10301313

 

TABLE B (SecurityUser)

SecurityUserIdUserIdDepartment
24Public Affairs
35Salem - Agency Affairs
1326Salem - Agency Affairs
1529Salem - Agency Affairs
4873Human Resources

 

TABLE C (User)

UserIdFullName
4Julie Waters
5Bobbi Doan
26Jim Gersbach
29Jason Cox
73Christy 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. 

VasTg
Memorable Member
Memorable Member

@SandeA 

 

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.

Connect on LinkedIn

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

 

Active Relationships.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

DataModel.jpg

VasTg
Memorable Member
Memorable Member

@SandeA 

 

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.

 

Connect on LinkedIn

@VasTg 

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? 

VasTg
Memorable Member
Memorable Member

@SandeA 

 

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

image.png

 

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)

Connect on LinkedIn

YES!! Thank you so much for all your help! This did the trick! I completely forgot about using the VAR options! 

VasTg
Memorable Member
Memorable Member

@SandeA 

 

Glad it helped.

 

Sometimes we even forget to eat. 🙂

 

Connect on Linkedin 

 

 

Connect on LinkedIn
VasTg
Memorable Member
Memorable Member

@SandeA 

 

If they linked as dotted line, they are inactive.

 

Could you share some mockup data and a screenshot of the data model?

 

 

Connect on LinkedIn

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors