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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## Virtual Table within a Calculated Column

Good Afternoon,

Looking for some assistance with a Calculated Column dealing with Inventory.

Have a Table of On Hand Inventory.

 Facility Item Pre-AdjustmentDate OnHandPreAdjustment Adjustment Date ABC ATA235 04/30/2023 1 05/01/2023

Have a Transaction Detail Table (simplified) as follows:

 Facility Item Transaction Type Date Qty. ABC ATA235 Inventory Adjustment 05/01/2023 -1 ABC ATA235 Inventory Adjustment 05/01/2023 1

Want to add a Calculated Column to the first table that sums the Qty in the second table for each Facility, Item and Date match.

I have been able to do it by creating a Summary Table and Lookup.

 Facility Item Pre-AdjustmentDate OnHandPreAdjustment Adjustment Date Adjustment ABC ATA235 04/30/2023 1 05/01/2023 0

Thinking there is a more efficient way by creating a virtual table within a Calculated Column, but haven't been able to get it to work.  Any guidance would be much appreciated.

Regards,

1 ACCEPTED SOLUTION
Super User

Something like this might work...

Adjustment =
var _vTable =
FILTER(
SUMMARIZE(
txTable,
txTable[Item],
txTable[Facility],
txTable[Date],
"sumQTY", SUM(txTable[Qty.])
),
txTable[Item] = onHandTable[Item] && txTable[Facility] = onHandTable[Facility] && txTable[Date] = onHandTable[Adjustment Date]
)
Return
SUMX(
_vTable,
[sumQTY]
)

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

3 REPLIES 3
Super User

Something like this might work...

Adjustment =
var _vTable =
FILTER(
SUMMARIZE(
txTable,
txTable[Item],
txTable[Facility],
txTable[Date],
"sumQTY", SUM(txTable[Qty.])
),
txTable[Item] = onHandTable[Item] && txTable[Facility] = onHandTable[Facility] && txTable[Date] = onHandTable[Adjustment Date]
)
Return
SUMX(
_vTable,
[sumQTY]
)

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

Thanks much for the prompt reply.

At first glance, appears to be what I am looking for.  Will give it a shot later today and let you know how I make out.

Thanks again!

Regards,

Super User

Just wanted to let you know that I got it to work as expected.  Thanks again for the assist.

Regards,

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors