Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
hpm
New Member

How to return values from table2's column if two columns from table2 are equal to table1's columns

Hello, 

 

My task is to create a custom column labeled "Assigned Hours" in the Cases query that returns Blocks[Assigned Block Hours] when Cases[SURGERY_DATE] = Blocks[SURGERY_DATE] and Cases[Room] = Blocks[Room]

 

At first, I tried:

if [SURGERY_DATE] = Blocks[SURGERY_DATE] and [Room] = Blocks[Room] then Blocks[Assigned Block Hours] else null 

 

Then, I tried Table.ContainsAll and List.PositionOf but I've been having issues since I'm trying to look up two values rather than one.

 

Here is my desired outcome:

SURGERY_DATESURGEONRoomAssigned Hours
7/17/2023SmithOR 19.5
7/17/2023JacksonOR 27.5
7/17/2023JennieOR 22
7/17/2023LacyOR 39.5

 

*This is just an example, not my actual data. Tables have different number of rows and columns. Any help would be greatly appreciated. Thank you.

 

Here are a few columns from Table 1 titled Cases

SURGERY_DATESURGEONRoom
7/17/2023SmithOR 1
7/17/2023Jackson OR 2
7/17/2023JennieOR 2
7/17/2023LacyOR 3
7/18/2023GreenOR 1
7/18/2023PaulOR 3
7/19/2023LacyOR 4
7/19/2023JacksonOR 4
7/20/2023Ollie

OR 6

Here are a few columns from Table 2 titled Blocks

SURGERY_DATERoomSERVICEAssigned Block Hours
7/17/2023OR 1Head and Neck9.5
7/17/2023OR 2Gynecology7.5
7/17/2023OR 2Orthopedics2
7/17/2023OR 3Plastics9.5
7/18/2023OR 1Urology7.5
7/18/2023OR 3Head and Neck6.5
7/19/2023OR 4Orthopedics4
7/19/2023OR 4General Surgery5
7/20/2023OR 5Ophthalmology

8.5

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)

View solution in original post

1 REPLY 1
wdx223_Daniel
Super User
Super User

=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.