Rank or Rownum group by multiple columns

[miinseon]

Hi All

I want to create a column with a rank or row number grouo by date and customer number.

I don't need a special order by column because i will only extract 1 values from the created column.

Date, Customer_number columns are what i have and Rank / Rownumber column is what i expected

Can i do this with Dax??

Thanks in advance 🙂

[mark_endicott · Accepted solution]

@miinseon - This can be done with DAX, but first you must add an Index column in Power Query. 

Follow these instructions to do this: https://support.microsoft.com/en-gb/office/add-an-index-column-power-query-dc582eaf-e757-4b39-98e6-b...

Once you have done this, the following DAX will work as a Calculated Column or Measure - you just need to change the table names:

VAR _index =
    CALCULATE ( MAX ( 'Table (6)'[Index] ) )
VAR _customer =
    CALCULATE ( MAX ( 'Table (6)'[Customer] ) )
VAR _date =
    CALCULATE ( MAX ( 'Table (6)'[Date] ) )
VAR _tmptable1 =
    FILTER (
        ALL ( 'Table (6)' ),
        'Table (6)'[Customer] = _customer
            && 'Table (6)'[Date] = _date
            && 'Table (6)'[Index] < _index
    )
VAR _tmptable2 =
    ADDCOLUMNS (
        _tmptable1,
        "@diff",
            'Table (6)'[Index]
                - MAXX (
                    FILTER (
                        ALL ( 'Table (6)' ),
                        'Table (6)'[Index] < EARLIER ( 'Table (6)'[Index] )
                            && 'Table (6)'[Customer] = EARLIER ( 'Table (6)'[Customer] )
                            && 'Table (6)'[Date] = EARLIER ( 'Table (6)'[Date] )
                    ),
                    'Table (6)'[Index]
                )
    )
VAR _max =
    MAXX ( _tmptable2, 'Table (6)'[Index] )
VAR _maxstart =
    MAXX ( FILTER ( _tmptable2, [@diff] > 1 ), 'Table (6)'[Index] )
VAR _tmptable3 =
    FILTER ( _tmptable2, 'Table (6)'[Index] >= _maxstart )
RETURN
    IF (
        ISBLANK ( _max ),
        1,
        IF ( _max = _index - 1, COUNTROWS ( _tmptable3 ) + 1, 1 )
    )

Screenshot shows it's working for me:

If this works for you, please mark it as the solution