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 and Approver are in two different tables.  

I was trying a conditional col - but don't think i can go to another table in that.  

I am also trying a calculated col - 

Revised-Approver = If('Invoice Header'[Invoice status] = "Paid", " ", MAX('LZ_Groups_Users Owner'[Approver]))
Don't think it is giving me the correct result.
Also tried a measure - but did not get the right results.
I was greatly appreciate your suggestions.
Many Thanks!!
 
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

6 Replies

  • jewel_at's avatar
    jewel_at
    Resolver 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
        Resolver 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