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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors