Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Vlookup between two tables to exclude Values

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

 

 

 

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

     

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you very much v-kkf-msft!

      It works!

      BR