Forum Discussion

Arnault_'s avatar
Arnault_
Icon for Resolver III rankResolver III
4 years ago
Solved

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 :

Perioditemcategorycategory_index
01/01/2021AX2
01/01/2021AY1
01/01/2021BX2
01/02/2021AY1
01/02/2021BX2
01/02/2021BY1
01/03/2021AX2
01/03/2021AY1

 

 

And the expected output:

Perioditemcategory
01/01/2021AX
01/01/2021BX
01/02/2021AY
01/02/2021BX
01/03/2021A

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

  • 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_'s avatar
      Arnault_
      Icon for Resolver III rankResolver III

      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 )

       

       

       

  • Arnault_ Please try:

    _MyTable2 =
    var _maxindex =MAX(Table_test[category_index])

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