Unique Serial Numbers even when we have duplicate records

[csb]

Hi I need help to get unique serial number for the rows when I go  to drillthrough page as shown in the image.If it retrieves 10 rec then serial number should be 1 to 10. If 5 records then 1 to 5 .I tried below DAX function but it is giving duplicate values because same vendor name,same facility.But still I need get unique numbers.How can I achieve this??

Below are the DAX I used:

1.Measure

SN1 = CALCULATE(COUNTROWS('Reporting PosStarting2022'),FILTER(ALLSELECTED('Reporting PosStarting2022'),'Reporting PosStarting2022'[Vendor_Nbr] <=MAX('Reporting PosStarting2022'[Vendor_Nbr])))

2.Calculated Column

SN = RANKX(ALLSELECTED('Reporting PosStarting2022'),SELECTEDVALUE('Reporting PosStarting2022'[Vendor_Nbr]),,ASC,DENSE)

Thanks..

[v-sdhruv]

Hi @csb ,

I hope the explaination provided, has addressed your query.
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out again by creating a new post.

Thank you

[v-sdhruv]

Hi @csb ,
Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

[v-sdhruv]

Hi @csb ,
Just wanted to check if you got a achance to review the suggestions provided and whether that helped you resolve your query?

Thank you @speedramps , @Greg_Deckler  and @Ashish_Mathur  for your insights to the query.

[Ashish_Mathur]

Hi,

This DAX pattern generates a serial number column

S. No. = ROWNUMBER(ALLSELECTED(Data),ORDERBY(Data[Date Presented]))

Hope this helps.

[Greg_Deckler]

@csb This is a common problem with the ranking functions in DAX. You could investigate this solution for getting unique ranks: The Mythical DAX Index - Microsoft Fabric Community

[speedramps · Accepted solution]

If you try doing it with a dax measure then you get inconistent results depending on the filters.

Consider doing it in Power Query, so your reports will show a consistent serial number for the rows.

For example ...

Create some test data

Group By Vendor using the Operator = All Rows

Add a custom column

Table.AddIndexColumn([Count],"Index",1)

Expand the Serial Number column

Remove unneeded columns

Congratualations, you now have a serial number for each row that restarts from 1 for each Vendor

Please click thumbs up because I have tried to help.

Then click [accept solution] if it works.

Thank you !