Remove duplicate rows from a filtered table

[oliverblane]

I want to apply a date filter to a table and then calculate a table from this, removing all duplicates in this calculated table and keeping the duplicates that have the most recent Created_Date.

I have a table (InitialTable) like the following that is to be filtered by a date:

If my applied date filter was from 01/07/2021 to 02/10/2021, for example, then the desired output table (CalculatedTable) would be:

Notice how both entries for ID 2 were in the date range, but only the most recent entry remains.

I know that something similar can be done in Power Query, but the issue is that I would like this to change based on the date filter. For example, in Power Query, the first two rows would have been removed, whereas if this date filter is set to 01/06/2021 - 01/08/2021, then I would like to still have those first two rows.

Hopefully I have explained my issue well enough - I am really grateful for any help I can get on this!

[v-chenwuz-msft · Accepted solution]

Hi @oliverblane ,

Not quite sure what your logic is for remove duplicate rows. Based on ID or based on id+Establishment.

IF ID, you can add an index column and try this code to keep the recent date rows.

Create_date_m =
VAR _maxdate =
    CALCULATE( MAX( 'Table'[Create_Date] ), ALLEXCEPT( 'Table', 'Table'[ID] ) )
VAR _c_index =
    CALCULATE(
        MAX( 'Table'[Index] ),
        FILTER( ALLEXCEPT( 'Table', 'Table'[ID] ), [Create_Date] = _maxdate )
    )
RETURN
    IF( MAX( 'Table'[Index] ) = _c_index, _maxdate, BLANK() )

And in the vsiual table , replace create_date with above measure. Use the slicer to filter date range.

The result:

I put my pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[oliverblane]

Thank you for this!

[amitchandak]

@oliverblane , First of all, slicer values will not filter calculated table or columns

Create a visual with ID , This measure

Measure =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( MAX ('Table'[Establishment] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[Date] = __date )

and Date , if needed take max date 

[oliverblane]

Right, I see. So it is not possible to remove duplicates based on a date filter?