topic Re: Get a list with only one individual with the latest next appointment date. in DAX Commands and Tips

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

Motomoto — Wed, 23 Oct 2024 14:01:05 GMT

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.

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

Selva-Salimi — Wed, 23 Oct 2024 14:38:20 GMT

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.

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

Motomoto — Thu, 24 Oct 2024 10:26:48 GMT

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

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

Selva-Salimi — Thu, 24 Oct 2024 10:54:49 GMT

Motomoto ,

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

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

Anonymous — Fri, 25 Oct 2024 05:21:47 GMT

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.

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

Motomoto — Fri, 25 Oct 2024 06:14:03 GMT

This solved, my problem. thank you all.