calculated column using userelationship

[Anonymous]

Dear Team,

I have a requirement where I need to create a calculated column in table B,which is a copy of column(username) from table A using a filter condition and userelationship since they have inactive connections through a field user_id.I know ,userelationship can only be used with calculate function but I am not sure how to use a calculated function to copy a column with text data type.plaese advise as necessary.Thanks in advance.

Regards

Rishi

[v-huizhn-msft]

Hi @Anonymous,

I create the sample tables and try to reproduce. And there is inactive relationship between them.



Then create a calculated column using the formula to get the copy username column from Table1.

Column = CALCULATETABLE( LASTNONBLANK(Table1[username],Table1[username]),USERELATIONSHIP(Table1[user_id],Table2[user_id]))

Please download the .pbix file and test it.

Best Regards,
Angelia

[Anonymous]

Dear @v-huizhn-msft

I tried your solution,but not getting as expected.I forgot to mention that I have an active connection from table A through another table.So when I put your code I am getting the answer which comes through active connection,seems like userelationship is not showing up.Thanks for your reply.

Regards

Rishi

[v-huizhn-msft]

Hi @Anonymous,

What's your mean? There is a middle table TableC btween TableA and TableC? There are one-to-one or one-to-many relationship? Could you please share more details for further analysis?

If there is a active relationship between TableA and TableB, you can use related function direcly as follows.



Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft

Table A 1----* (active connection)[key:user_id] Table B

Table B 1----* (active connection)[key:gift_id] Table C

Table C *---1 Table A [key:user_id] (inactive connection).

This is the scenario and I need to get username from table A using key user_id in table C which is currently inactive connection.

Thanks for ur help.Looking forward for your reply.

regards

rishi

[v-huizhn-msft]

Hi @Anonymous,

I add another table3, try to reproduce your scenario, the related function still works in Table3. Please review the following screenshot.



Best regards,
Angelia

[Anonymous]

hi @v-huizhn-msft

In table 3 also there is user_id field,and I need to point that to table A (here connection is inactive) to get corresponding username from table A.

Regards,

Rishi

[v-huizhn-msft]

Hi @Anonymous,

The related still works fine as follows. And there is no nessary to create a relationship between table1[user_id] and table3[user_id], tabl1 and table3 can be connected by table2.

 

[Anonymous]

Hi @v-huizhn-msft

when I use related,column is empty....dont know where is the issue.Thank you for your time

Regards

Rishi

[v-huizhn-msft]

Hi @Anonymous,

Your relationship is one-one or one-many relationship? Could you please share your pbix file for further analysis?

Best regards,
Angelia

[Anonymous]

hi @v-huizhn-msft

I have earlier posted the relations

Table A  to Table B -[1----many]-(active connection)[key:user_id] 

Table B to Table C -[1----many]- (active connection)[key:gift_id] 

Table C to Table A -[many----1]-[key:user_id] (inactive connection).

Regards

Rishi

[v-huizhn-msft]

Hi @Anonymous,

Please create a sample table, so that we can post the solution which is close to your requirement. I totally understand your data is private, you create similar structure fake data for instance.

Best Regards,
Angelia

[Anonymous]

tables

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

For your screenshot, we can get the username from 'user' table. Becasue the 'user‘ table connect 'Redeem' table indirectly by Discount[discount_materid] and Discount[discount_created]. For example, you can get usename by Discount[discount_created], then you can get Discount[username] in 'Redeem' Table based on Discount[discount_materid] and Redeem[discount_materid]. While your requirement is to get username based on [redeemed_by], the value of Redeem[discount_materid] and Redeem[redeemed_by] are different, so the relationship can not be active.

For your requirement, you can copy a user table(named user1 here), then create a relationship between 'Redeem' and 'user1' table, select user1[username] as value. Please download the file and test.


Best Regards,
Angelia

[Anonymous]

Hello @v-huizhn-msft

sorry for the delay as I was busy with other module.I have created a sample and I will post the relationships image as well as table required.The username in the fifth column of the table is not correct since the relation is inactive between redeem and user.The username for redeemed_by id=3 is cc not aa.I am also attaching the pbix

Regards

Rishi