Forum Discussion

Ackbar-Learner's avatar
Ackbar-Learner
Resolver I
3 years ago
Solved

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...
DAX Window Function
  • OwenAuger's avatar
    OwenAuger
    3 years ago

    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's avatar
Ackbar-Learner
Resolver I
3 years ago

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