Forum Discussion
How to create column that counts the specific duplicate entry in the table
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 |
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.
4 Replies
- amitchandak
Super User
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]) )
- 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.
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-queryIf this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
