Forum Discussion
Filter / Summarize Table by Max Value index
Hi All,
I have one table with duplicated rows. I would like to remove these duplicates. It can be achieved by using one column and keeping the highest index. Here is a simple example. For each period and each item, I would like to have only 1 row.
I have tried this formula:
_MyTable2 =
var _maxindex = CALCULATE ( MAXX (Table_test, Table_test[category_index] ) , ALLEXCEPT ( Table_test, Table_test[item] ) )
RETURN
FILTER (Table_test, Table_test[category_index] = _maxindex )
In January, I have 2 rows for item A and I would like to keep only the one with the max index (=2)
In January, I have 1 row for item B and I want to keep it. My formula works.
However, in February I should have 1 row for each item with same logic. In that case, the row with the item A is not retreived, just like if the index of A (1) is compared with the index of B (2). Please see the 2 tables below (initial and expected). I hope you can help me solve this. Thanks in advance.
The initial table :
| Period | item | category | category_index |
| 01/01/2021 | A | X | 2 |
| 01/01/2021 | A | Y | 1 |
| 01/01/2021 | B | X | 2 |
| 01/02/2021 | A | Y | 1 |
| 01/02/2021 | B | X | 2 |
| 01/02/2021 | B | Y | 1 |
| 01/03/2021 | A | X | 2 |
| 01/03/2021 | A | Y | 1 |
And the expected output:
| Period | item | category |
| 01/01/2021 | A | X |
| 01/01/2021 | B | X |
| 01/02/2021 | A | Y |
| 01/02/2021 | B | X |
| 01/03/2021 | A | X |
Arnault_ ,
You can create a flag column new column
=
var _max - maxx(filter(Table, [Period] = earlier([Period]) && [item] = earlier([item] ) ) , [category_index] )
return
if([category_index] =_max, false(), true())
3 Replies
- amitchandak
Super User
Arnault_ ,
You can create a flag column new column
=
var _max - maxx(filter(Table, [Period] = earlier([Period]) && [item] = earlier([item] ) ) , [category_index] )
return
if([category_index] =_max, false(), true())
- Arnault_
Resolver III
Thanks for your ansnwer.
I actually created 2 columns applying the same logic:
Max_index = CALCULATE ( MAX ( Table_test[category_index] ), ALLEXCEPT ( Table_test, Table_test[item],Table_test[Period] ) )Max_Index_YorN = If ( Table_test[Max_index] = Table_test[category_index], 1, 0 )
- David-Ganor
Resolver II
Arnault_ Please try:
_MyTable2 =var _maxindex =MAX(Table_test[category_index])RETURNMAXX(FILTER (Table_test, Table_test[category_index] = _maxindex ),Table_test[category])