Solved: How to count duplicate text values in a column and... - Page 2

mic_anghileri · 01-10-2022 06:41 AM

Good morning,

I need your help to create an expression that results in the most repeated value in a column and how many times it has been repeated.

In the “School” table I have a column called “Student Name” where the data (name) is entered by the user.

I wish I could find the most entered name and know how many times it was entered.

Let me explain myself worse.

For example, in the “Student Name” column there are these names:

So Sandro appears 3 times, Mario 2 times, Michele 1 time, Monica 1 time, Lucia 1 time, Paolo 1 time.

I would like the function to return only: Sandro (3).

Would any of you know how to tell me how to do it? Thanks in advance everyone for the help.

WillowMobileSys

@Marc_Dillon 's approach above is a good one.

If you are not opposed to adding additional columns to help facilitate the selection, this is how I would approach it:

Add a virtual Column to your Students table to count the number of occurrences, maybe [Frequency], and the expression would be:

COUNT( FILTER("School", [Student] = [_THISROW].[Student Table Key]))

This does assume you have in the School table a REF column to a Students table.

Where ever you need to show the “winning Student” add another Virtual Column, maybe [Winning Student] as a REF column and assign it using a MAXROW() expression.

MAXROW("Students", "Frequency")

[Winning Student].[Student Name] & " (" & [Winning Student].[Frequency] & ")"

One big question is how do you plan to handle the display when there are multiple students with the same “winning” count?

How to count duplicate text values in a column and select only the most frequent?