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 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)

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

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

    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.

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