Forum Discussion
Ackbar-Learner
3 years agoResolver I
Help with New Window function
Hi before i used below DAX to calculate running totals and it works fine. Board02P&LRunningTotal = if(
HASONEFILTER(TabPLGLItem[GLItem])
,
CALCULATE(
[Board01P&LAllTrans...
- 3 years ago
To replicate the behaviour of your original measure using the WINDOW function, a measure like this should work:
Board02aP&LRunningTotal = CALCULATE ( [Board01P&LAllTransactionAmountwithSign], WINDOW ( 0, ABS, 0, REL, ALL ( TabPLGLItem[GLItem], TabPLGLItem[Index] ), ORDERBY ( TabPLGLItem[Index] ) ) )Does this work as intended?
Explanation:
- In order to order by TabPLGLItem[Index] for the running total, you must include TabPLGLItem[Index] in the Relation argument of WINDOW, and provide TabPLGLItem[Index] as the argument of ORDERBY.
- Your original measure used ALL to ignore existing filters (explicitly for TabPLGLItem[GLItem] and implicitly for TabPLGLItem[Index]), so ALL should be used when specifying the Relation argument as well.
Regards,
Owen
OwenAuger
3 years agoSuper User
To replicate the behaviour of your original measure using the WINDOW function, a measure like this should work:
Board02aP&LRunningTotal =
CALCULATE (
[Board01P&LAllTransactionAmountwithSign],
WINDOW (
0, ABS,
0, REL,
ALL ( TabPLGLItem[GLItem], TabPLGLItem[Index] ),
ORDERBY ( TabPLGLItem[Index] )
)
)
Does this work as intended?
Explanation:
- In order to order by TabPLGLItem[Index] for the running total, you must include TabPLGLItem[Index] in the Relation argument of WINDOW, and provide TabPLGLItem[Index] as the argument of ORDERBY.
- Your original measure used ALL to ignore existing filters (explicitly for TabPLGLItem[GLItem] and implicitly for TabPLGLItem[Index]), so ALL should be used when specifying the Relation argument as well.
Regards,
Owen
Ackbar-Learner
3 years agoResolver I
Superb! It worked. Thanks.
Does the window function work with text or we should still keep an index column?