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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
miinseon
Helper I
Helper I

Rank or Rownum group by multiple columns

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 🙂

miinseon_0-1719476924939.png

 

1 ACCEPTED SOLUTION
mark_endicott
Super User
Super User

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

 

mark_endicott_1-1719485716012.png

 

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

View solution in original post

1 REPLY 1
mark_endicott
Super User
Super User

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

 

mark_endicott_1-1719485716012.png

 

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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