Add a column showing number of duplicates and the duplicate values change based a filter

[Trikity]

Hello,

Per the sample table below, I would like to add a column (3rd colunm on the table) on the existing data table, showing # of duplicates in the Topic column, which is something I was able to do from various sources online.

Is there a way for this value to dynamically change so that it would show 1 for A and B, when I filtered to Name 1?

Thank you in advance!

Nick

Name	Topic	# of duplicates in the Topic column
Name 1	A	2
Name 2	A	2
Name 1	B	2
Name 2	B	2

[v-sdhruv]

Hi @Trikity ,
If the explaination has helped you gain some insight on the issue, can you please mark it as a "Accept as Solution"?
This will help other community members reach the solution easily.
Thanks!

[v-sdhruv · Accepted solution]

Hi @Trikity ,
The duplicate counts wont change dynamically based on a filter.
I thought you needed help with duplicate counts.
Calculated columns in Power BI are computed once when the data model is loaded or refreshed, and they do not respond to slicers or filters applied in reports.
That’s because calculated columns exist at the row level in the data model, and Power BI assumes a fixed, unchanging context for them.
Hope the explaination helps!

[Trikity]

Thank you for a thorough explanation, I really appreciate it! I've searched for solution for many hours and I guess there isn't a way to have the row count responds to slicer or filter action.

Thanks everyone for trying to assist!

[v-sdhruv]

Hi @Trikity ,

You can achieve this by Power Query or by DAX.

Through Power query:

1.Go to transform data

2.Select the column where you want to find duplicates.

3. Click on Group By.

4. In the Group By window, Group by the column containing the values you want to check for duplicates.

5. In the New column name, type something like Count of Duplicates. Under Operation, choose Count Rows.

Through DAX:

Duplicate Count =
CALCULATE(
COUNTROWS('Data'),
FILTER('Data', 'Data'[Name] = EARLIER('Data'[Name]))
)

Hope this helps!
If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Trikity]

I can't seem to get the value in each row to dynamically changed, based on the filter from the slicer such as filtering to a single name. 

[rajendraongole1]

Hi @Trikity  - you can absolutely make the # of duplicates in the Topic column respond dynamically to slicers or filters like when you select Name 1

If you want the count to update based on filters like Name, create measure as below:

Topic Duplicates (Measure) :=
CALCULATE(
COUNTROWS('YourTable'),
ALLEXCEPT('YourTable', 'YourTable'[Topic])
)

Use the measure version in your table visual — it dynamically updates based on any slicers (like filtering to "Name 1")

Hope this helps.

[Trikity]

Hello @rajendraongole1,

Thank you so much for the information. I may have missed something or not using it correctly but I did as instructed but the value still doesn't change.

As per the top image below, my table name is For Employee Records Change - Worksheet Table and the duplicate values that I am looking for is in the Certification Code column. The top part showing the measure that I created. The table showing the # of duplicates on the far right column.

The issue I am having is that Faysel only have 1 certification 1412108292R in his record but the value stays at 3 when I filtered out his name, it should have change to 1 (bottom image)