Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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!!
 
1 ACCEPTED SOLUTION
jewel_at
Helper II
Helper II

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

View solution in original post

6 REPLIES 6
jewel_at
Helper II
Helper II

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

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.

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

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

Anonymous
Not applicable

Hi Jewel,

It is working fine.  THANK YOU!

🙂 

Nice! 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.