Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
smpa01
Super User
Super User

Incorporating additional methods in PARTITION/ORDER

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

smpa01_0-1718633742107.png

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

smpa01_1-1718633829383.png

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

View solution in original post

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

@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
AntrikshSharma
Super User
Super User

@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_0-1718637156122.png

 

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

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

AntrikshSharma_0-1718638350135.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.