New Column based on values in another table

[pavanb]

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

[jewel_at · Accepted solution]

Hi @pavanb 

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

[pavanb]

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]

Hi @pavanb 

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

[pavanb]

I was trying to create a conditional column for this - but i guess you can't use a different table for conditional column.

Is that correct?

Thanks

[pavanb]

Hi Jewel,

It is working fine.  THANK YOU!

🙂 

[jewel_at]

Nice!