Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
Shipping Table:
Expected output from Forecast measure:
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**
)
)
Solved! Go to Solution.
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.
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])
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.
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])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
83 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
129 | |
108 | |
63 | |
55 |