Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 🙂
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:
Open Power BI or your data modeling tool.
Open the Power Query Editor (if using Power BI) or the Data Model (if using Excel or another tool) to create a calculated column.
Create a new calculated column with the following DAX formula:
Serial Number =
RANKX(
ALL('YourTableName'),
CALCULATE(MAX('YourTableName'[Values])),
,
DESC,
Dense
)
Replace 'YourTableName' with the actual name of your table.
Explanation of the DAX Formula:
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.
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 :
Why don't you add an Index column to your data table starting from 0. That way, every row will have a unique number.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
70 | |
57 | |
37 | |
36 |
User | Count |
---|---|
85 | |
65 | |
60 | |
46 | |
45 |