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

Super User

## Incorporating additional methods in PARTITION/ORDER

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
Super User

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] ) )``````
5 REPLIES 5
Super User

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] ) )``````
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Community Champion

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

Super User

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Community Champion

@smpa01 see if this works:

``````VAR Temp =
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,