How to create Automatically generate serial number

[Mariano92]

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. 

Thanks in advance 🙂 

[123abc]

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.

[JadhavVarsha_13]

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 :

[TotunG]

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