Identify group with 3 or more rows and filter the one with the latest date.

[user35131]

I have a datatset that looks like this

ID	Last Name	First Name	Date
101	Andrews	John	02/05/2022
101	Andrews	John	04/20/2022
101	Andrews	John	12/04/2020
101	Andrews	John	11/30/2020
102	Harper	Luke	09/11/2021
102	Harper	Luke	7/05/2021
103	Clark	Mark	10/02/2021
103	Clark	Mark	12/01/2021
103	Clark	Mark	12/05/2021
104	Le Fleur	Jack	09/03/2020
105	Johnson	Jim	02/08/2021

I would like to set a filter that would leave me with this dataset

ID	Last Name	First Name	Date
101	Andrews	John	04/20/2022
103	Clark	Mark	12/05/2021

I' am grouping by ID, Last Name, and First Name but selecting the latest Date.

[v-zhangti · Accepted solution]

Hi, @user35131 

You can try the following methods.

Measure:

latest date = 
IF (
    SELECTEDVALUE ( 'Table'[Date] )
        = IF (
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) )
            ) >= 3,
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER ( ALL ( 'Table' ), [ID] = SELECTEDVALUE ( 'Table'[ID] ) )
            ),
            BLANK ()
        ),
    1,
    0
)

Drag Measure into the view of the original table and set it equal to 1.

Is this the output you expect?

Best Regards,

Community Support Team _Charlotte

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

[mahenkj2]

Hi @user35131 ,

I can suggest you a possible solution in power query as below:

Group rows as below:

This will create a column with all the rows of combined groups, just to get the number of records in that, and later we filter >=3 as below:

Final solution should be as intended:

Removed unwanted columns.

Hope it helps.

[user35131]

I want to only select only the ones with 3 or more rows. That would include IF(ROWCOUNT?

[PC2790]

Ahh Ok!

I have implemented both the solution in the sample file attached, you can choose as per your convenience.

1) Using Power Query - It utilises the concept of grouping, expanding, extracting the max date and removing duplicates.

2) Using Dax-It uses the Summaize dax function to do the grouping and then filtering out  the columns having rowcount>2

[PC2790]

Hey @user35131 ,

If you are looking to summarize based on ID,First Name and Last Name then you can use dax as below:

NewTable = ADDCOLUMNS(
    SUMMARIZE( SampleTable, SampleTable[ID],SampleTable[First Name],SampleTable[Last Name] ),
    "Max", CALCULATE(MAX(SampleTable[Date] ) )
)

It will give you the outcome as below:

As per your given expected output, I don't understand the logic behind having only two selected columns as grouping using ID, FName and LName, Max Date against all IDs will be returned.

See if the above proposed solution caters to your requirement