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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
handrade
Frequent Visitor

retrieve column value from current iterated row

Hello all,

 

I would like to create a measure that sums a column based on a filter I apply. The query gets complex beacuse I need to retrieve the column value from the current iterated row. My context is the following:

 

Production table:
Production.PNG

 

 

 

 

 

Shipping Table:

Shipping.PNG

 

 

 

 

 

 

 

 

 

 

Expected output from Forecast measure:

 

Forecast.PNG 

 

 

 

 

 

 

 

 

 

 

 

 

 

  

 

 

 

So far this is the code I've come up with, but like mentioned above I haven't found a way to retrieve the current column value from the iterated row. I might be thinking this the incorrect way. If there is another way that you can think for calculating this measure I will appreciate if you share your thoughts.

 

Forecast = 
    SUM('Production'[Production Quantity]) 
    - 
    SUMX('Shipping', 
FILTER('Shipping',
'Shipping'[Requested Ship Date] <= **Column value being iterated by row in SUMX**
)

 

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

Hi handrade,

 

Please follow steps below:

 

1. Click Edit Queries-> Merge Queries as New to merge 'Production' and 'Shipping' into one table based off Part Number.

Capture.PNG 

2. Click Add Column-> Index Column to create a index column so that the iteration can be implemented by Index Column.

3. After Close&Apply, create a calculate column running total to achieve the cummulative total of Requested Quantity, then you can achieve the Forecast.

Running Total = 
CALCULATE (
    SUM ( Merge1[Requested Quantity] ),
    FILTER (
        Merge1,
        Merge1[Index] <= EARLIER ( Merge1[Index] )
            && Merge1[Part Number] = EARLIER ( Merge1[Part Number] )
    )
)

Forecast = MAX(Merge1[Production Quantity]) - MAX(Merge1[Running Total])
2.PNG 
 
Regards,
Jimmy Tao

 

View solution in original post

1 REPLY 1
v-yuta-msft
Community Support
Community Support

Hi handrade,

 

Please follow steps below:

 

1. Click Edit Queries-> Merge Queries as New to merge 'Production' and 'Shipping' into one table based off Part Number.

Capture.PNG 

2. Click Add Column-> Index Column to create a index column so that the iteration can be implemented by Index Column.

3. After Close&Apply, create a calculate column running total to achieve the cummulative total of Requested Quantity, then you can achieve the Forecast.

Running Total = 
CALCULATE (
    SUM ( Merge1[Requested Quantity] ),
    FILTER (
        Merge1,
        Merge1[Index] <= EARLIER ( Merge1[Index] )
            && Merge1[Part Number] = EARLIER ( Merge1[Part Number] )
    )
)

Forecast = MAX(Merge1[Production Quantity]) - MAX(Merge1[Running Total])
2.PNG 
 
Regards,
Jimmy Tao

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.