Conditional Aggregation with 2 tables and inactive relationships

[dospencer]

I have a model from which I need to determine the number of new students in a Fall term who have pledged (PledgedInd=1) in the Spring term immediately after the Fall term and are also retained (RetainedInd=1) in the next Fall term.
The tables involved in the model, sample data and the relationships are shown below. It has 2 inactive relationship because it wouldn't allow direct active relationship because active indirect relationships already existed.

Based on the sample data, I should get only 1 student (StudentID=1) who has pledged in the Spring and Retained in the Fall but I'm getting back 2 with the following measure:
Pledged and Retained =

CALCULATE(SUM(SpringPledge[PledgedInd])
,FILTER(FallRetention,FallRetention[RetainedInd]=1)
,FILTER(SpringPledge,Spring_Pledge[PledgedInd]=1)
,USERELATIONSHIP('DWH Academic_Term_Dim'[Term_Key_First_Fall],'First Fall Retention'[Term_Key])
,USERELATIONSHIP('DWH Academic_Term_Dim'[Term_Key_Next_Excluding_Summer], Spring_Pledge[Term_Key])
)

How do I get the right results?

Student
StudentID, TermCode
1, 202009
2, 202009
3, 202009

Term
TermCode, Term, TermCodeNextSpring, TermCodeNextFall
202009, Fall, 202101, 202109
202101, Spring, 202201, 202109

SpringPledge
StudentID, TermCode, PledgedInd
1, 202101, 1
2, 202101, 1
3, 202101, 0

FallRetention
StudentID, TermCode, RetainedInd
1, 202109, 1
2, 202109, 0
3, 202109, 1

Active Relationships
Term.TermCode to Student.TermCode
Student.StudentID to SpringPledge.StudentID
Student.StudentID to FallRetention.StudentID

Inactive Relationships
Term.TermCodeNextSpring to SpringPledge.TermCode
Term.TermCodeNextFall to FallRetention.TermCode

[dospencer]

Thank you. Your suggestions worked in resolving my use case.

[amitchandak]

@dospencer , Term, and student are dimensions with two facts Spring and Fall and the two facts should not join with each other, You should create two measures one from each fact, and make a decision based on that

If need you can add column sping and fall in tables and append them Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

I think you need measures like (refer to use of isblank and how use values )

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...

If this does not help
Can you share a sample output in table format? Or a sample pbix after removing sensitive data.