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

 

 

 

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

14 Replies

  • 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