Help with New Window function

[Ackbar-Learner]

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

[Ackbar-Learner]

@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 

[tamerj1]

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

[OwenAuger · Accepted solution]

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

[Ackbar-Learner]

Superb! It worked. Thanks.

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

[OwenAuger]

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.