How to Add Serial Number in Power BI Table
Adding a serial number or row number column to a table in Power BI can be achieved by using DAX expressions and calculated columns. Here’s a detailed explanation with examples:
Example 1: Using ROW() Function
To add a simple sequential serial number column to a table, you can use the ROW() function:
SerialNumber = ROW()
This calculated column will assign a unique serial number to each row in the table based on its position.
Example 2: Adding Resetting Serial Number by Group
If you want the serial number to reset for each distinct group or category in your table, you can use a combination of functions.
First, create a calculated column to determine the group index:
GroupIndex =
RANKX(ALL('Table'[Category]), 'Table'[Category], , ASC)
Then, create another calculated column to generate the serial number within each group:
SerialNumber =
RANKX(FILTER('Table', 'Table'[GroupIndex] = EARLIER('Table'[GroupIndex])), 'Table'[ID], , ASC)
This will assign a unique serial number within each group, based on the ‘ID’ column.
Example 3: Customizing Serial Number Format
If you want to customize the serial number format, such as adding prefixes or leading zeros, you can modify the calculated column expression accordingly. Here’s an example:
SerialNumber =
"SN-" & RIGHT("0000" & [ID], 5)
This will prefix the serial number with “SN-” and pad the ‘ID’ column with leading zeros to a length of 5 characters.
Note:
After creating the calculated column(s), you can add them to your table visual in Power BI to display the serial numbers.