topic Re: Summarize and Filter to keep only the first time a customer came to our school in DAX Commands and Tips

Summarize and Filter to keep only the first time a customer came to our school

mlovejoy — Mon, 28 Nov 2022 09:27:36 GMT

Hello community,

I m trying to create a table or a column to retrieve unique Student ID and their respective date of the first time they came in our school from the enrollment table.

What I've tried so far:

So Student ID musst be unique and the date should be the first time he came to our school. So Minimum start date by Student ID.

Any help? Should be simple but can't do it 🙂

Thanks for you help

Re: Summarize and Filter to keep only the first time a customer came to our school

Anonymous — Mon, 28 Nov 2022 11:24:37 GMT

Hi mlovejoy

Please find this below solution.

Test table = SUMMARIZE('Table','Table'[Student id],"start_date",MIN('Table'[datteofstart]))

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: Summarize and Filter to keep only the first time a customer came to our school

NikhilChenna — Mon, 28 Nov 2022 11:27:03 GMT

Hi mlovejoy , You can achieve this by this below solution.

1. Go the modelling tab and click on the new table option.

2. Use this below logic,

Summarize_table =

SUMMARIZE(

'Enrollments',

'Enrollments'[StudentID],
"Min Intake Year",MIN('Enrollments'[Intake Year])

"Min date",MIN('Enrollments'[Date starting])

)

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: Summarize and Filter to keep only the first time a customer came to our school

mlovejoy — Mon, 28 Nov 2022 12:42:12 GMT

Hello NikhilChenna ,

thanks for your answer. Howerver Intake Year is a text column Q1 2022, S2 2023,... so there is no minimum. Would there be a possiblity to add a column in the Student table to get this information? Basically I want to use Min start date as a criteria to put the related Intake Year (Q2 2022, S2 2023, ...) comming from the enrollment table in every student ID of the student table.

Thanks

Mark

Re: Summarize and Filter to keep only the first time a customer came to our school

mlovejoy — Mon, 28 Nov 2022 12:46:19 GMT

Hello Anonymous ,

thanks for your answer. For every Student ID I need to get the Intake Year which is text (Q2 2022, S1 2023,..). So actually the Date Min date start is the criteria to filter adn get the Intake Date.

Regards

Mark

Re: Summarize and Filter to keep only the first time a customer came to our school

mlovejoy — Mon, 28 Nov 2022 12:55:15 GMT

NikhilChenna Anonymous I'm doing this in DAX and it works, except that I want to retrieve the Intake Year of that "related Min Starting Date". Any ideas 🙂

Thanks a lot for your help.

Re: Summarize and Filter to keep only the first time a customer came to our school

NikhilChenna — Mon, 28 Nov 2022 13:24:53 GMT

Hi mlovejoy ,

It is easy to map, but for that you have to create a unique key between 2 tables.

1. Create a concat of studentid and date using this in both the tables, =CONCATENATE('table1'[studentID],TEXT('table1'[Date],"mmddyyyy")

2. Create a new column in the summarized table, using lookup function,

Calculated column = LOOKUPVALUE('table1'[Intake Year],'table1'[concat],'summarizedtable'[concat])

This will solve your issue.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!