topic Re: Help with New Window function in DAX Commands and Tips

Help with New Window function

Ackbar-Learner — Sat, 21 Jan 2023 14:48:17 GMT

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

Re: Help with New Window function

OwenAuger — Sat, 21 Jan 2023 20:07:30 GMT

Hi Ackbar-Learner

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

Re: Help with New Window function

Ackbar-Learner — Sat, 21 Jan 2023 20:13:55 GMT

Superb! It worked. Thanks.

Does the window function work with text or we should still keep an index column?

Re: Help with New Window function

tamerj1 — Sat, 21 Jan 2023 20:25:30 GMT

Hi Ackbar-Learner

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]
)

Re: Help with New Window function

OwenAuger — Sat, 21 Jan 2023 20:38:45 GMT

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.

Re: Help with New Window function

Ackbar-Learner — Sun, 22 Jan 2023 08:15:52 GMT

tamerj1 OwenAuger

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