LookUp Measure with Multiple Columns

[ogend]

Hello Power BI Gurus, 

Can you please help me with DAX for "Needed" Column?

I need to find a corresponding record for "Opposite Enity" and "Opposite Entry Type" combination  against "Entity"  and "Entry Type"  combination and  get amount from "Amount Column" .

Amount is a simple measure - it takes amount from "Amount USD" Table native Column and filters against calendar table (time intelligence year slicer)

"Opposite Entity" and "Opposite Entity Type" are Table columns

Individual values for Entity, Opposite Entity, Entry Types and Opposite Entry Types  repeat, however "Entity /Entry Type/ Opposite Entity/ Opposite Entry Type"  combinations are unique.

I tried merging the table to itself to handle this without DAX, but the table has many columns and it needs to be merged by multiple fields, I was not able to figure out how to do this without duplicating rows. 

Entity	Entry Type	Opposite Entity	Opposite Entry Type	Amount	Needed Column
001	a paid	003	a received	40	39
001	b received	006	b paid	100	100
001	c paid	004	c received	90	80
002	a paid	004	a received	200	199
002	b received	005	b paid	1	0
003	a received	001	a paid	39	40
004	c received	001	c paid	80	90
004	a received	002	a paid	199	200
005	b paid	002	b received	0	1
006	b paid	001	b received	100	100
007	d paid	010	d received	500	n/a

Thank you!

[PC2790]

Hey @ogend ,

You can create a new custom column by concatenating multiple columns(the combination of which is unique). This custom column will act as a key in merging the table with itself and then you can extract the desired amount column.