Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
@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
Solved! Go to 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] ) )
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] ) )
@AlexisOlson many thanks for this. I did not know about ROWNUMBER at all.
@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.
@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
@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,
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
33 | |
25 | |
24 | |
23 |