Forum Discussion

MrMarshall's avatar
MrMarshall
Helper II
8 years ago
Solved

New column based on multiple sorted rows

Trying to create a new column based on multiple sorted rows within the same OrderNumber.

I have the following schema:

DescriptionOrderNumberOrderRow
Art 13000010
Art 23000020
Art 33000030
Art 43000110
Art 53000120
Art 63000130

I want to make a new column with the Description of the rows with the lowest OrderRow value within the same OrderNumber.

Which would be: 

DescriptionOrderNumberOrderdRowNew Column
Art 13000010Art 1
Art 23000020Art 1
Art 33000030Art 1
Art 43000110Art 4
Art 53000120Art 4
Art 63000130Art 4

 

I have no idea where to begin, probably due lack of DAX-syntax knowledge. Or maybe this is easier in the query editor.

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    Hi MrMarshall,

     

    You could create a calculated column with dax expression below.

     

    New Column =
CALCULATE (
    MAX ( 'Order'[Description] ),
    FILTER (
        'Order',
        'Order'[OrderNumber] = EARLIER ( 'Order'[OrderNumber] )
            && 'Order'[OrderRow] = MIN ( 'Order'[OrderRow] )
    )
)

    Then you will get your expected output.

     

     

    Hope this can help you!:smileytongue:

     

    Best Regards,

    Cherry

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    Hi MrMarshall,

     

    You could create a calculated column with dax expression below.

     

    New Column =
CALCULATE (
    MAX ( 'Order'[Description] ),
    FILTER (
        'Order',
        'Order'[OrderNumber] = EARLIER ( 'Order'[OrderNumber] )
            && 'Order'[OrderRow] = MIN ( 'Order'[OrderRow] )
    )
)

    Then you will get your expected output.

     

     

    Hope this can help you!:smileytongue:

     

    Best Regards,

    Cherry

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        8 years ago

        Hi MrMarshall,

         

        It is glad that we can help. Only thing that you'll have to notice, just always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly. 

         

        Best Regards,

        Cherry