cancel
Showing results 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.

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:

Any idea what might went wrong with the window function?

Thanks

1 ACCEPTED SOLUTION
Super User

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
Blog
5 REPLIES 5
Resolver I

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

Super User

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

Super User

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
Blog
Resolver I

Superb! It worked. Thanks.

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

Super User

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
Blog

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors