cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How to create Automatically generate serial number

Hello,
The table below shows my data structure. I want to create a measure to automatically generate a serial number starting from 1, even if I use filters.

My problem is that the values are not unique. In the Serial Number column I have represented the sequence as it should be, the Name column is not valid only Values.

 Values Name Serial Number 2 aaa 6 2 bbb 5 2 ccc 4 3 ddd 3 4 eee 1 4 ffff 2 1 ggg 7 1 hhh 8 1 iii 9

I try to use Rankx function but I think I should add some more columns to make a correct filtatrion.

3 REPLIES 3
Community Champion

To automatically generate a serial number starting from 1, even when using filters, you can create a calculated column in your table using DAX. You can use the RANKX function along with other DAX functions to achieve this. Here's a step-by-step guide:

Assuming your table is named "YourTableName," and you want to create a calculated column called "Serial Number," follow these steps:

1. Open Power BI or your data modeling tool.

2. Open the Power Query Editor (if using Power BI) or the Data Model (if using Excel or another tool) to create a calculated column.

3. Create a new calculated column with the following DAX formula:

Serial Number =
RANKX(
ALL('YourTableName'),
CALCULATE(MAX('YourTableName'[Values])),
,
DESC,
Dense
)

1. Replace 'YourTableName' with the actual name of your table.

2. Explanation of the DAX Formula:

• RANKX: This function calculates the rank of each row based on the calculated value inside the CALCULATE function.
• ALL('YourTableName'): This ensures that all rows in the table are considered, regardless of any filters applied.
• CALCULATE(MAX('YourTableName'[Values])): This calculates the maximum value of the "Values" column for the current row, effectively assigning a rank based on the maximum value.
• , DESC: This sorts the ranks in descending order.
• Dense: This ensures that the ranks are dense, meaning there are no gaps in the numbering.
3. Once you create the calculated column, it will automatically generate the serial number for each row, even when you apply filters.

With this calculated column in place, you will have a unique serial number assigned to each row in your table based on the maximum "Values" within the unfiltered dataset. It will update dynamically when you apply filters, maintaining the sequence correctly.

Keep in mind that calculated columns are static and consume memory in your data model. If you have a very large dataset, consider the impact on performance and memory usage when adding calculated columns.

Helper V

hello @Mariano92

You can try using RANKX dax as below:

Calculated Column = RANKX('TableName',TableName'[Serial No],,ASC,Dense)

or you can also add index column which starts from 1 directly as below :

Resolver I

Why don't you add an Index column to your data table starting from 0. That way, every row will have a unique number.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors