topic Re: Incorporating additional methods in PARTITION/ORDER in DAX Commands and Tips

Incorporating additional methods in PARTITION/ORDER

smpa01 — Mon, 17 Jun 2024 14:20:20 GMT

TLDR: How can I incorporate additional calculation in PARTITION/ORDER without creating any calculated column?

is SQL I can do this, where I account for additional calculation for partitioning in the window function itself.

How can I replicate this in dax without needing me to create any additional columns at all. i am reluctant ro create additional coulmns cause they have no utility other than serving correct value for PARTITION

Thank you in advance

Re: Incorporating additional methods in PARTITION/ORDER

AntrikshSharma — Mon, 17 Jun 2024 15:04:52 GMT

smpa01 PARTITIONBY doesn't support virtual columns yet, so you can round the Amount column in PQ itself you don't need to store 4 decimal places for this value unless it is a different field in the actual data.

Re: Incorporating additional methods in PARTITION/ORDER

smpa01 — Mon, 17 Jun 2024 15:13:14 GMT

AntrikshSharma I can't do that, additional methods on original column would have undesired effect elsewhere, therefore the original value needs to be preserved regardless.

jeffrey_wang any chance of incorporating this in future? The following works but it so much more easier to work with same SQL convention in DAX

Re: Incorporating additional methods in PARTITION/ORDER

AntrikshSharma — Mon, 17 Jun 2024 15:33:28 GMT

smpa01 see if this works:

VAR Temp = ADDCOLUMNS ( ALL ( 'Table'[Amount], 'Table'[Index] ), "@Rounded Amount", ROUNDDOWN ( 'Table'[Amount], 2 ) ) VAR CurrentAmountRounded = ROUNDDOWN ( 'Table'[Amount], 2 ) VAR CurrentIndex = 'Table'[Index] VAR FilterTable = FILTER ( Temp, [@Rounded Amount] = CurrentAmountRounded && 'Table'[Index] <= CurrentIndex ) RETURN COUNTROWS ( FilterTable )

I tried to enter some data not sure if this is what you want because this column doesn't make sense to me,

Re: Incorporating additional methods in PARTITION/ORDER

AlexisOlson — Mon, 17 Jun 2024 22:15:41 GMT

This is how I'd like to write it, but Function PARTITIONBY cannot be used with columns added by DAX table functions.

ROWNUMBER ( SELECTCOLUMNS ( t3, t3[Index], "@Round", ROUND ( t3[Amount], 2 ) ), ORDERBY ( t3[Index], ASC ), PARTITIONBY ( [@Round] ) )

The alternative is to partition manually.

VAR RoundedAmount = ROUND ( t3[Amount], 2 ) VAR Partition = FILTER ( SELECTCOLUMNS ( t3, t3[Index], t3[Amount] ), ROUND ( t3[Amount], 2 ) = RoundedAmount ) RETURN ROWNUMBER ( Partition, ORDERBY ( t3[Index] ) )

Re: Incorporating additional methods in PARTITION/ORDER

smpa01 — Tue, 18 Jun 2024 00:10:18 GMT

AlexisOlson many thanks for this. I did not know about ROWNUMBER at all.