Forum Discussion

SandeA's avatar
SandeA
Helper III
6 years ago
Solved

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 ...
  • VasTg's avatar
    VasTg
    6 years ago

    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

     

    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's avatar
SandeA
Helper III
6 years ago

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. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VasTg's avatar
VasTg
Memorable Member
6 years ago

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.

 

  • SandeA's avatar
    SandeA
    Helper III
    6 years ago

    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's avatar
      VasTg
      Memorable Member
      6 years ago

      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

       

      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's avatar
        SandeA
        Helper III
        6 years ago

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