Explanation of my source table: I have column E which has data-validation dropdowns for Reviewer numbers, and column R which has traffic transaction details. Reviewer now check each of the transaction (Column R), and once review is done, will click on the dropdown (Column E) and select Reviewer# 1, to indicate that the review is completed.
In a separate tab, I have the pivot table which has a Power Pivot measure to count the number of items reviewed by the Reviewer; =COUNT(Ops_Table[Image Reviewer's Name]). This works great and displays the value of all the transactions which were reviewed.
Where I am facing trouble:
Sometimes Column R can have duplicate values, which means that same vehicle has transmitted redundant data, for example in the attached picture, Veh Trx ID - 270871136 was displayed thrice. When redundant trx ID were created, reviewer will mark his/ her name in Column E to indicate that these trx were reviewed, but since it is the same Transaction, we want to count it only once, and not thrice.
If it were just a table, i could have used COUNTIFS, but since it is a pivot table wherein I need to display my count, I am having an additional layer of ambiguity, since when I create a measure, I observed it does not support COUNTIFS.
Can somebody please help in providing ideas on how should i display the "Count Items in Column E while making sure that Column R Trx ID is unique and not repeating." Please let me know if you need me to further explain the problem. Thanks a lot in advance.
Please note: I cannot be using VBA, so please provide me with a non-VBA response. Thanks.
In a separate tab, I have the pivot table which has a Power Pivot measure to count the number of items reviewed by the Reviewer; =COUNT(Ops_Table[Image Reviewer's Name]). This works great and displays the value of all the transactions which were reviewed.
Where I am facing trouble:
Sometimes Column R can have duplicate values, which means that same vehicle has transmitted redundant data, for example in the attached picture, Veh Trx ID - 270871136 was displayed thrice. When redundant trx ID were created, reviewer will mark his/ her name in Column E to indicate that these trx were reviewed, but since it is the same Transaction, we want to count it only once, and not thrice.
If it were just a table, i could have used COUNTIFS, but since it is a pivot table wherein I need to display my count, I am having an additional layer of ambiguity, since when I create a measure, I observed it does not support COUNTIFS.
Can somebody please help in providing ideas on how should i display the "Count Items in Column E while making sure that Column R Trx ID is unique and not repeating." Please let me know if you need me to further explain the problem. Thanks a lot in advance.
Please note: I cannot be using VBA, so please provide me with a non-VBA response. Thanks.
