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

[mlovejoy]

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

[Anonymous · Accepted solution]

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!

[mlovejoy · Accepted solution]

@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.

[NikhilChenna · Accepted solution]

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!

[NikhilChenna]

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!

[mlovejoy · Accepted solution]

@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.

[mlovejoy]

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

 

[Anonymous · Accepted solution]

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!

[mlovejoy]

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