Incorporating additional methods in PARTITION/ORDER

[smpa01]

@AlexisOlson @AntrikshSharma @jeffrey_wang 

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

[AlexisOlson · Accepted solution]

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

[smpa01]

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

[AntrikshSharma]

@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.

[smpa01]

@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

[AntrikshSharma]

@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,