cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

Resolver II

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors