Skip to main content
cancel
Showing results for 
Search instead 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

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
Community Champion
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.

@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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.