Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Mariano92
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 NameSerial Number 
2aaa6
2bbb5
2ccc4
3ddd3
4eee1
4ffff2
1ggg7
1hhh8
1iii9


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

Thanks in advance 🙂 

3 REPLIES 3
123abc
Community Champion
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.

JadhavVarsha_13
Resolver II
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 :

JadhavVarsha_13_0-1694697636778.jpeg

 

TotunG
Resolver I
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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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