topic Re: New Column based on values in another table in DAX Commands and Tips

New Column based on values in another table

Anonymous — Thu, 26 Jan 2023 18:27:50 GMT

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

Re: New Column based on values in another table

jewel_at — Thu, 26 Jan 2023 19:00:38 GMT

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

Re: New Column based on values in another table

Anonymous — Thu, 26 Jan 2023 19:55:59 GMT

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.

Re: New Column based on values in another table

jewel_at — Fri, 27 Jan 2023 13:20:39 GMT

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

Re: New Column based on values in another table

Anonymous — Fri, 27 Jan 2023 16:52:21 GMT

Hi Jewel,

It is working fine. THANK YOU!

🙂

Re: New Column based on values in another table

jewel_at — Fri, 27 Jan 2023 17:55:29 GMT

Nice!

Re: New Column based on values in another table

Anonymous — Thu, 02 Feb 2023 14:16:39 GMT

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