Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

New Column based on values in another table

Hello, My end user wants to see Null in the Approver field if the Invoice Status is 'Paid' - and wants to see the Approver (field value) if the Invoice Status is other than 'Paid'.  Invoice Status a...
New column - based on value in another table
  • jewel_at's avatar
    jewel_at
    3 years ago

    Hi Anonymous 

     

    You can try this, but this would only work if you have a relationship set between your Invoice Header and LZ_Groups_Users Owner tables.

     

    Revised-Approver = if ('Invoice Header'[Invoice Status] = "Paid", BLANK(), RELATED('LZ_Groups_Users Owner'[Approver]))

     

    If you use MAX the value will be the same for all that's not Paid, and it will be the last Approver on your table. 

     

     

    Hope this helps!

    Jewel

jewel_at's avatar
jewel_at
Icon for Resolver I rankResolver I
3 years ago

Hi Anonymous 

 

You can try this, but this would only work if you have a relationship set between your Invoice Header and LZ_Groups_Users Owner tables.

 

Revised-Approver = if ('Invoice Header'[Invoice Status] = "Paid", BLANK(), RELATED('LZ_Groups_Users Owner'[Approver]))

 

If you use MAX the value will be the same for all that's not Paid, and it will be the last Approver on your table. 

 

 

Hope this helps!

Jewel

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks Jewel.  I appreciate it.

    there is a one to many relationship between the LZ groups users owner table and the Invoice Header table. 

    From LZ Groups Users Owner to Invoice - 1 direction.  

    Will this work? I've tried it but not getting the right result.

    • jewel_at's avatar
      jewel_at
      Icon for Resolver I rankResolver I
      3 years ago

      Hi Anonymous 

       

      Just confirming, is your relationship primary key set as Owner ID? And did you create the new column in the Invoice Header table?

       

      I've attached a sample test .pbix here:

      Condition Get from Related table 

       

      It would be great too if you can give us your sample data and screenshot of your model.

       

      Hope this helps!

      Jewel

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Jewel,

        It is working fine.  THANK YOU!

        🙂