Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ackbar-Learner
Resolver I
Resolver 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&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:

AckbarLearner_0-1674312399674.png

 

Any idea what might went wrong with the window function?

 

Thanks

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

5 REPLIES 5
Ackbar-Learner
Resolver I
Resolver I

@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

 

AckbarLearner_0-1674374669705.png

I did the same running total using the previous way and it worked.

 

AckbarLearner_1-1674375297646.png

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
Super User
Super User

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
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.