Forum Discussion
Highest value of duplicate
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
3 Replies
- Nathaniel_C
Community Champion
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 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: WrongExample: 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
