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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rsbin
Super User
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
jgeddes
Super User
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]
)

View solution in original post

3 REPLIES 3
jgeddes
Super User
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]
)

@jgeddes ,

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,

@jgeddes ,

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

Regards,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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