topic Re: Help Needed: Generating Sequential Serial Numbers in Power BI with Date Slicer in DAX Commands and Tips

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

SBC — Mon, 21 Oct 2024 11:57:36 GMT

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!

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

Rupak_bi — Mon, 21 Oct 2024 14:05:34 GMT

hi SBC

here is you solution

measure:

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

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

SBC — Tue, 22 Oct 2024 03:00:15 GMT

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.

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

Rupak_bi — Tue, 22 Oct 2024 05:23:43 GMT

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.

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

SBC — Tue, 22 Oct 2024 05:37:17 GMT

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

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

Rupak_bi — Tue, 22 Oct 2024 06:21:27 GMT

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.

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

Anonymous — Tue, 22 Oct 2024 07:19:34 GMT

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.