Get a list with only one individual with the latest next appointment date.

[Motomoto]

I need help with creating a new table that will list only people with the latest next appointment date information of each individual.No duplicates should apperar in the new table.

[Anonymous · Accepted solution]

Hi @Motomoto ， Selva-Salimi， thank you for your prompt reply!

To meet your requirements, follow these steps:

Step 1: First, find the maximum HIV-ART Next follow-up date for each Program ID using the MAX function.

Step 2: Then, for the rows with the same maximum HIV-ART Next follow-up date, find the corresponding maximum Date of visit using the MAX function.

Step 3: Use DISTINCT to ensure that the final result contains unique rows.

UniqueProgramData = 
DISTINCT(
    FILTER(
        'Table',
        'Table'[HIV-ART Next follow-up date] = 
        CALCULATE(
            MAX('Table'[HIV-ART Next follow-up date]),
            ALLEXCEPT('Table', 'Table'[Program ID])
        ) &&
        'Table'[Date of visit] = 
        CALCULATE(
            MAX('Table'[Date of visit]),
            ALLEXCEPT('Table', 'Table'[Program ID], 'Table'[HIV-ART Next follow-up date])
        )
    )
)

Result for your reference:

Best regards,

Joyce

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

[Motomoto]

This solved, my problem. thank you all.

[Motomoto]

Hello Selva. Thank you very much, the solution is very helpful, but i still have an issue as i still get some duplicates especially if the next follow up dates are within the same month. Thank you very much

[Selva-Salimi]

@Motomoto ,

the next follow up date should just used in max function. if so, would you please share the measure you have written.

[Selva-Salimi]

hi @Motomoto 

you can create this table as follows: (go modelling >> new table)

summarize( your_table , and write the name of any column you want to appear in this table (note that the values in these columns should be unique per person) , "latest_date" , max (nex_followup_date))

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.