Help Needed: Generating Sequential Serial Numbers in Power BI with Date Slicer

[SBC]

Hello ,

I'm working on a Power BI report and need assistance with generating sequential serial numbers. My data includes columns for "Name" and "Account" (in string format), and I use a date column as a slicer.

Objective:
I want to add a serial number to each name in the table visual. When filtering by date, the serial numbers should remain sequential (1, 2, 3, ...) without any gaps.

Challenge:
When users select a date using the slicer, I want the serial numbers to regenerate in sequence for the filtered names associated with that specific date. The goal is to ensure no gaps appear in the numbering.

Sample Data:

Name Account Date

John Smith	ACC12345	2024-10-01
Maria Garcia	ACC67890	2024-10-02
Li Wei	ACC54321	2024-10-01
Emily Johnson	ACC33445	2024-10-01

Expected Output When Filtering by "2024-10-01":

Serial Number Name Account

1	John Smith	ACC12345
2	Li Wei	ACC54321
3	Emily Johnson	ACC33445

Has anyone implemented a similar solution or can suggest a DAX formula or method to achieve this? Any guidance would be greatly appreciated!Thank you in advance for your help! 

[Rupak_bi · Accepted solution]

hI @SBC 

The logic is doing exactly the same here are some more scenarios I have added to the dataset

And here are the outputs

the logic is simply partitioning based on date. 

[Anonymous · Accepted solution]

Hi @SBC 

Thanks for the reply from Rupak_bi . 

The following testing is for your reference.

Sample:

1. Add an index column in Power Query(Transform data)

2. Create a calculated table as the slicer

Date = VALUES('Table'[Date])

3. Create two measures as follow.

Index1 = MAX([Index])

Rank = 
VAR _selectedDate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    IF (
        _selectedDate = BLANK (),
        RANKX ( ALL ( 'Table' ), [Index1],, ASC, DENSE ),
        IF (
            MAX ( [Date] ) = _selectedDate,
            RANKX (
                FILTER ( ALL ( 'Table' ), [Date] = _selectedDate ),
                [Index1],
                ,
                ASC,
                DENSE
            ),
            BLANK ()
        )
    )

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

hi @SBC 

here is you solution

measure:

rank = CALCULATE( RANK(all('Table'),FIRST,PARTITIONBY('Table'[Date])))

[SBC]

Thanks @Rupak_bi , for responding to my query. However, the solution isn't working as expected, as it is displaying incorrect serial numbers instead of the expected ones.

[Rupak_bi]

Hi @SBC 

Based on the sample data you shared, I hope it was working. If you could share sample data which represents your dataset( not actual values) , would be better to check the error. However normally rankx is being used to generate index if you have unique values in a column ( like date or numbers). But here it is alphabets to be sequenced. 

[SBC]

Hi @Rupak_bi ,

The logic we are aiming for is to display a serial number for each row in the table visual, starting from S.No: 1, 2, 3, and so on, regardless of the data or level present in the table.

Table Visual :

S.NO	Name	Account	Event
1	AA	aqwer	FA
2	BB	qdwty	CA
3	CC	outre	BA
4	FF	twer	PA

[Rupak_bi · Accepted solution]

hI @SBC 

The logic is doing exactly the same here are some more scenarios I have added to the dataset

And here are the outputs

the logic is simply partitioning based on date.