Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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_DATE | SURGEON | Room | Assigned Hours |
7/17/2023 | Smith | OR 1 | 9.5 |
7/17/2023 | Jackson | OR 2 | 7.5 |
7/17/2023 | Jennie | OR 2 | 2 |
7/17/2023 | Lacy | OR 3 | 9.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_DATE | SURGEON | Room |
7/17/2023 | Smith | OR 1 |
7/17/2023 | Jackson | OR 2 |
7/17/2023 | Jennie | OR 2 |
7/17/2023 | Lacy | OR 3 |
7/18/2023 | Green | OR 1 |
7/18/2023 | Paul | OR 3 |
7/19/2023 | Lacy | OR 4 |
7/19/2023 | Jackson | OR 4 |
7/20/2023 | Ollie | OR 6 |
Here are a few columns from Table 2 titled Blocks
SURGERY_DATE | Room | SERVICE | Assigned Block Hours |
7/17/2023 | OR 1 | Head and Neck | 9.5 |
7/17/2023 | OR 2 | Gynecology | 7.5 |
7/17/2023 | OR 2 | Orthopedics | 2 |
7/17/2023 | OR 3 | Plastics | 9.5 |
7/18/2023 | OR 1 | Urology | 7.5 |
7/18/2023 | OR 3 | Head and Neck | 6.5 |
7/19/2023 | OR 4 | Orthopedics | 4 |
7/19/2023 | OR 4 | General Surgery | 5 |
7/20/2023 | OR 5 | Ophthalmology | 8.5 |
Solved! Go to Solution.
=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)
=Table.AddColumn(Cases,"Assigned Hours",each Blocks{[SURGERY_DATE=[SURGERY_DATE],Room=[Room]]}?[Assigned Block Hours]?)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
9 | |
8 | |
7 |
User | Count |
---|---|
17 | |
10 | |
7 | |
6 | |
6 |