Forum Discussion

tgalla01's avatar
tgalla01
Frequent Visitor
3 years ago
Solved

Counting/Sequencing based on multiple columns within the same table

Hi everyone! I hope this is a quick one for somebody...

 

I have an "orders" table that I need to insert a new DAX column into. I prefer to avoid M if possible due to the table size. I need to count/sequence the orders by customers for analysis. I'm struggling to find a suitable data/code example to use so any quick help would be very appreciated. Here is a sample of what I have and what I'm after. 

Column 1 - Customer_ID from the orders table
Column 2 - Order (ie order numbers) also from the orders table.

Column 3 is the new DAX column for the order count. I need the count to use the incrementing order number by customer_id.

Thank you!!


Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, tgalla01 

     

    You can try the following methods.
    Column:

    OrderCount = 
CALCULATE (
    COUNT ( 'Table'[Order] ),
    FILTER (
        'Table',
        [Order] <= EARLIER ( 'Table'[Order] )
            && [Customer_ID] = EARLIER ( 'Table'[Customer_ID] )
    )
)
    RANK = 
RANKX (
    FILTER ( 'Table', [Customer_ID] = EARLIER ( 'Table'[Customer_ID] ) ),
    [Order],
    ,
    ASC
)

    Both methods will get the results you expect.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Bifinity_75's avatar
    Bifinity_75
    Solution Sage
    3 years ago

    Hi tgalla01 , try this measure:

    OrderCount = RANKX(FILTER('Table', 'Table'[Customer_ID] = EARLIER('Table'[Customer_ID])), RANKX(ALL('Table'), 'Table'[Order]), , DESC, Dense)

    Best regards

     

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, tgalla01 

     

    You can try the following methods.
    Column:

    OrderCount = 
CALCULATE (
    COUNT ( 'Table'[Order] ),
    FILTER (
        'Table',
        [Order] <= EARLIER ( 'Table'[Order] )
            && [Customer_ID] = EARLIER ( 'Table'[Customer_ID] )
    )
)
    RANK = 
RANKX (
    FILTER ( 'Table', [Customer_ID] = EARLIER ( 'Table'[Customer_ID] ) ),
    [Order],
    ,
    ASC
)

    Both methods will get the results you expect.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.