How to create column that counts the specific duplicate entry in the table

[sjpbi]

Hi, need help on How to create column that counts the specific duplicate entry in the table?

Here's my sample data table

ID	RequestID
013D	34567
023S	56789
056F	98765
013R	34567
098T	98765
067H	67843

Need to have this count column in pbi dax referencing from request id and id to count the first entry as 1

ID	RequestID	Count
013D	34567	1
023S	56789	1
056F	98765	1
013R	34567	2
098T	98765	2
067H	67843	1

[Anonymous · Accepted solution]

Hi @sjpbi ,

Please follow these steps:

1.In Power Query -- add Column – Index Column – From 1.

the results are shown below.

2.Create a calculated column and write the following expression.

Count Column = COUNTROWS(FILTER('Table','Table'[RequestID] = EARLIER('Table'[RequestID]) && 'Table'[Index] <= EARLIER('Table'[Index])))

3.The final result is shown below.

If you don't want the index column to appear in the report, you can hide it by clicking on the small eye to its right.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[Ritaf1983]

Hi @sjpbi 
I think that this is much easier to solve from a power query, take a look at the linked tutorial:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

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

[Ashish_Mathur]

Hi,

It is fairly easy to do this in the Query Editor.  If you are OK with using the Query Editor, then post back.

[amitchandak]

@sjpbi , Try with new Dense Rank function in a measure

Date would be a better option for order by, you can use if ([New Rank Desne]>= 2, 2,1)

New Rank Desne = rank(DENSE,ALLSELECTED(Table[ID],Table[RequestID]),ORDERBY(Table[RequestID],asc),, partitionBy(Table[ID]) )