Filter / Summarize Table by Max Value index

[Arnault_]

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

[David-Ganor]

@Arnault_ Please try:

_MyTable2 =

var _maxindex =MAX(Table_test[category_index])

RETURN

MAXX(FILTER (Table_test, Table_test[category_index] = _maxindex ),Table_test[category])

[amitchandak · Accepted solution]

@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_]

@amitchandak ,

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 )