DAX Measure Help

[LIWO]

Hello

I've got this setup in Power BI:

I'm trying to create a measure, which can provide the "startdate" of a specific "type" (Tabel 1) for a visual with a list of "SocialSecurityNo" from Tabel 3.
But I can't seem to get the date for a specific student - I get the same (max)date for everybody. I have tried a lot of formulas and completely lost track of them 😉
Anyone who can help me with a measure, I can try?

Regards,
Lisa

[LIWO]

Thank you so much @ryan_mayu . It works perfectly 🙂

[LIWO]

Thank you so much @ryan_mayu - it works perfectly! 

[LIWO]

I have a visual with SocialScurityNo.
I need to add for exaple the startdate for type "VFO" if the student has any "type VFO" - else it need to be blank.
Do this answer your question?

[ryan_mayu · Accepted solution]

i think your data model is not good.

table 2 and 3 are linked by student ID. That is a many to many relationship.

ID 300 may have two social security no and each one will have two agreeement?

I used a,b,c,d,e for social security No.

measure =

VAR _id=maxx(FILTER(Table2,Table2[StudentID]=max(Table3[StudentID])),Table2[AgreementID])

VAR _type=maxx(FILTER(Table1,Table1[AgreementID]=_id),Table1[Type])

return if(_type="VFO",maxx(FILTER(Table1,Table1[AgreementID]=_id),Table1[Start]),blank())

pls see the attachment below.

[LIWO]

Sorry. In tabel 3 there is only one Student ID per SpcialSecurityNo, so it will be fine, I guess. I will try to translate your measure into my datamodel 🙂 

[ryan_mayu]

what's the expected output based on the sample data you provided?