Vlookup between two tables to exclude Values

[Anonymous]

Hello everyone.

I have got the following situation:

I have the main table named "OD". In this table is a column named "DocNr,Years,Bkrs" and a column named "NettingAmount"(imaginary values in this case because of confidentiality).

I have a second table named "Definitionen". In this table is a column named "Formel".

Now i want to implement a so called vlookup which says: If the Value from the column "DocNr,Years,Bkrs" is also part of the column "Formel", than dont take the Netting Amount of it.

So that I only have the Netting Amounts which are not part of the column "Formel" from the table "Definitionen".

Does anyone have a solution for this?

Thank you very much in advance!

BR

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I create the following sample data.

   

Then create a new column:

Column = IF ( NOT [DocNr,Years,Bkrs] in VALUES ( Definitionen[Formel] ), [NettingAmount] )

Or create the measure:

Measure = 
CALCULATE (
    SUM ( OD[NettingAmount] ), 
    EXCEPT ( VALUES ( OD[DocNr,Years,Bkrs] ), VALUES ( Definitionen[Formel] ) )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much @v-kkf-msft!

It works!

BR

[amitchandak]

@Anonymous , you have lookupvalue, that you can try

refer if needed

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8