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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors