Forum Discussion
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&LAllTransactionAmountwithSign]
,
ALL(TabPLGLItem[GLItem])
,
TabPLGLItem[Index] <= VALUES(TabPLGLItem[Index])
)
,
blank()
)
Now I am trying my hand at the new Window function and wrote following DAX:
Board02aP&LRunningTotal =
CALCULATE(
[Board01P&LAllTransactionAmountwithSign],
WINDOW(
0,ABS,
0,REL,
SUMMARIZE(ALLSELECTED(TabPLGLItem),TabPLGLItem[GLItem]),
ORDERBY(TabPLGLItem[GLItem])
)
)Unfortunately, it is not giving me the same result. See below screenshot:
It is returning the same previous measure, which was calculating amount per row item:
Any idea what might went wrong with the window function?
Thanks
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
5 Replies
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
Superb! It worked. Thanks.
Does the window function work with text or we should still keep an index column?
Great! 🙂
In order to specify the sort order of a text column (by anything other than its natural sort order), yes, you would need to keep the index column, and specify it as the argument of ORDERBY.
You nned to have an item index column. please try
Board02aP&LRunningTotal =
SUMX (
WINDOW (
0,
ABS,
0,
REL,
SUMMARIZE (
ALLSELECTED ( TabPLGLItem ),
TabPLGLItem[GLItem],
TabPLGLItem[ItemIndex]
),
ORDERBY ( TabPLGLItem[ItemIndex] )
),
[Board01P&LAllTransactionAmountwithSign]
)Both your solutions worked except that there is one limitation with the window function. Doing a running total with data from Direct Query using a window function is limited to 1million rows
I did the same running total using the previous way and it worked.
This seems weird though 🤔 as I kept everything the same and only changed the running total dax code. I guess the workings of the window function is pretty different at the back. Let me know if you have any workaround for this.
Thanks
