Daily DAX : Day 269 ROWNUMBER

 The `ROWNUMBER` function in Power BI DAX (Data Analysis Expressions) assigns a unique sequential integer to each row within a specified partition of a table, based on a defined sort order. It’s useful for ranking, indexing, or numbering rows in a dataset for reporting and analysis purposes.

### Syntax

```dax

ROWNUMBER ( <OrderBy>, [ <PartitionBy> ], [ <MatchBy> ] )

```

- **OrderBy**: Defines the column(s) and sort direction (ASC or DESC) to determine the row order. This is required.

- **PartitionBy** (optional): Specifies column(s) to group rows into partitions. Row numbering restarts at 1 for each partition.

- **MatchBy** (optional): Defines additional columns to resolve ties in the `OrderBy` clause, ensuring deterministic numbering.

### How It Works

- `ROWNUMBER` generates a sequence of numbers starting from 1 for each row in the result set.

- If `PartitionBy` is specified, the numbering resets to 1 for each group defined by the partition.

- The `OrderBy` clause determines the sequence based on the specified column(s) and sort direction.

- If `MatchBy` is used, it ensures consistent numbering when there are ties in the `OrderBy` values.

### Use Cases

1. **Ranking Rows**: Assign a rank or index to rows based on a metric, such as sales or performance, for leaderboards or ordered lists.

2. **Row Indexing**: Create a unique row identifier within a table or partition for easier reference in reports or calculations.

3. **Paginated Reports**: Number rows for display in paginated reports or to facilitate navigation through large datasets.

4. **Top-N Analysis**: Combine with filters to identify top-ranked items within partitions, like top customers per region.

5. **Serial Numbering**: Add sequential numbers to rows in a visual, such as invoices or transaction lists.

### Example

Suppose you have a sales table with columns `Region`, `Salesperson`, and `SalesAmount`.

#### DAX Formula

```dax

SalesRank = ROWNUMBER (

    ORDERBY ( 'Sales'[SalesAmount], DESC ),

    PARTITIONBY ( 'Sales'[Region] )

)

```

#### Explanation

- **OrderBy**: Sorts rows by `SalesAmount` in descending order (highest sales get lower row numbers).

- **PartitionBy**: Groups rows by `Region`, so numbering restarts for each region.

- **Result**: Each salesperson gets a rank (starting from 1) within their region based on sales.

#### Sample Output

| Region  | Salesperson | SalesAmount | SalesRank |

|---------|-------------|-------------|-----------|

| East    | Alice       | 500         | 1         |

| East    | Bob         | 300         | 2         |

| West    | Charlie     | 400         | 1         |

| West    | Dave        | 200         | 2         |

### Key Notes

- **Performance**: `ROWNUMBER` is optimized for large datasets but can be resource-intensive with complex partitions or sorting.

- **Ties**: Without `MatchBy`, ties in `OrderBy` values may result in arbitrary numbering. Use `MatchBy` to enforce consistency (e.g., by adding a unique column like `Salesperson`).

- **Limitations**: `ROWNUMBER` is only available in newer versions of Power BI (introduced in 2023). Ensure your Power BI version supports it.

- **Alternative**: For older versions, you can use `RANKX` or calculated columns with earlier DAX patterns, though they’re less straightforward.

### Practical Example in Power BI

1. Create a table visual with `Region`, `Salesperson`, and `SalesAmount`.

2. Add a calculated column or measure using `ROWNUMBER` to assign ranks.

3. Use the rank in visuals or filters, e.g., to show only the top 5 salespeople per region.