Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

calculated column using userelationship

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 in...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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