topic Re: Adding Calculated Columns with data linked through 3 tables in DAX Commands and Tips

Adding Calculated Columns with data linked through 3 tables

SandeA — Tue, 28 Jan 2020 16:28:25 GMT

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

Re: Adding Calculated Columns with data linked through 3 tables

VasTg — Tue, 28 Jan 2020 16:40:41 GMT

SandeA

If they linked as dotted line, they are inactive.

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

Re: Adding Calculated Columns with data linked through 3 tables

SandeA — Tue, 28 Jan 2020 17:58:08 GMT

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

Re: Adding Calculated Columns with data linked through 3 tables

SandeA — Thu, 30 Jan 2020 15:11:14 GMT

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.

Re: Adding Calculated Columns with data linked through 3 tables

VasTg — Thu, 30 Jan 2020 15:15:36 GMT

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.

Re: Adding Calculated Columns with data linked through 3 tables

SandeA — Thu, 30 Jan 2020 17:56:29 GMT

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.

Re: Adding Calculated Columns with data linked through 3 tables

VasTg — Thu, 30 Jan 2020 21:36:20 GMT

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.

Re: Adding Calculated Columns with data linked through 3 tables

SandeA — Thu, 30 Jan 2020 21:47:06 GMT

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?

Re: Adding Calculated Columns with data linked through 3 tables

VasTg — Thu, 30 Jan 2020 22:15:33 GMT

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)

Re: Adding Calculated Columns with data linked through 3 tables

SandeA — Thu, 30 Jan 2020 22:21:56 GMT

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

Re: Adding Calculated Columns with data linked through 3 tables

VasTg — Thu, 30 Jan 2020 22:33:35 GMT

SandeA

Glad it helped.

Sometimes we even forget to eat. 🙂

Connect on Linkedin