Forum Discussion

csb's avatar
csb
Frequent Visitor
1 year ago
Solved

Unique Serial Numbers even when we have duplicate records

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..

 

 

 

 

Need Help
  • speedramps's avatar
    speedramps
    1 year ago

    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 !

     

     

     

     

     

     

     

     

6 Replies

  • speedramps's avatar
    speedramps
    Super User
    1 year ago

    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 !

     

     

     

     

     

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    This DAX pattern generates a serial number column

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

     

  • v-sdhruv's avatar
    v-sdhruv
    Community Support
    1 year ago

    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's avatar
    v-sdhruv
    Community Support
    1 year ago

    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