Highest value of duplicate

[Anonymous]

Hi,

i have 3 columns column 1 is unique, column 3 and 4 can have duplicates.

Now i need to add a column to identify which has the highest value in column 1 when its a duplicate.

Example:

Index	Id	Name
1	A21	red
2	A21	red
3	A22	green
4	A23	yellow
5	A22	green
6	A24	blue
7	A21	red
8	A22	green
9	A24	blue

I need to have the higehst value marked with yes in a caalculated column 4

Index	Id	Name	Highest value
1	A21	red	No
2	A21	red	No
3	A22	green	No
4	A23	yellow	Yes
5	A22	green	No
6	A24	blue	No
7	A21	red	Yes
8	A22	green	Yes
9	A24	blue	Yes

Any idea to solve it? Any help is welcome 🙂

Regards

[je_s]

My "index" column is not unique (it's actually a duration column counting stop time) - Is there a way to return one of the vaules? 

example: Wrong

Example: Correct

[Nathaniel_C]

Hi @je_s ,

You will need some data that will differentiate which value to return if the values match - like the two values of 600.  You can either insert an index column, or I notice that your column has the word time in the header.  You could make a rule of taking the first or the last row based on the date by using the time value.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous ,

Try this:

Highest value = 

var _curName = mycolor[Name]
var _curIndex = mycolor[Index]
var _calc = CALCULATE(MAX(mycolor[Index]),FILTER(ALLEXCEPT(mycolor,mycolor[Name]), mycolor[Name]=_curName))

return IF(_calc = _curIndex, "Yes", "No")

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel