cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ogend
Helper I
Helper I

LookUp Measure with Multiple Columns

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. 

EntityEntry TypeOpposite EntityOpposite Entry TypeAmount       Needed Column
001a paid003a received4039
001b received006b paid100100
001c paid004c received9080
002a paid004a received200199
002b received005b paid10
003a received001a paid3940
004c received001c paid8090
004a received002a paid199200
005b paid002b received01
006b paid001b received100100
007d paid010d received500n/a

 

Thank you!

1 REPLY 1
PC2790
Super User
Super User

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.

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors