Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Craines
Helper II
Helper II

Select specific date based on filter criteria

Hi, I have two tables containing student application data as follows:

 

StudentTable

StudentIdStatusChangeDateDeferral?AppDate
Student1A101/10/2020TRUE01/10/2020
Student1Os205/10/2020TRUE01/10/2020
Student1Oz506/10/2020TRUE01/10/2020
Student2Aa507/10/2020FALSE 
Student2R3311/10/2020FALSE 
Student3A7h02/10/2020TRUE02/10/2020
Student3O104/10/2020TRUE02/10/2020
Student3AC805/10/2020TRUE02/10/2020
Student4A106/10/2020TRUE06/10/2020
Student4O309/10/2020TRUE06/10/2020
Student4O310/10/2020TRUE06/10/2020

 

StatusTable (is much larger - only showing relevant codes) - linked to StudentTable on Status

StatusApplicationOfferAcceptance
A1100
Aa5100
A7h100
Os2110
Oz5110
O1110
O3110
AC8111
R33100

 

I have calculated the AppDate column using the following:

 

DeferAppDate = IF( 'StudentTable'[Deferral?] = True, MINX( FILTER('StudentTable', 'StudentTable'[StudentId] = EARLIER( 'StudentTable'[StudentId])), 'StudentTable'[ChangeDate]))
 
I would now like a column which picks up the date at which the first offer is made to look as follows:
StudentIdStatusChangeDateDeferral?AppDateOfferDate
Student1A101/10/2020TRUE01/10/202005/10/2020
Student1Os205/10/2020TRUE01/10/202005/10/2020
Student1Oz506/10/2020TRUE01/10/202005/10/2020
Student2Aa507/10/2020FALSE  
Student2R3311/10/2020FALSE  
Student3A7h02/10/2020TRUE02/10/202004/10/2020
Student3O104/10/2020TRUE02/10/202004/10/2020
Student3AC805/10/2020TRUE02/10/202004/10/2020
Student4A106/10/2020TRUE06/10/202009/10/2020
Student4O309/10/2020TRUE06/10/202009/10/2020
Student4O310/10/2020TRUE06/10/202009/10/2020

 

I am struggling as this is neither the MAX or MIN date, so I haven't been able to use the same approach as for the AppDate (which will always be the first date), and wonder if the approach needs to involve "Offer = 1" from the StatusTable?

 

Thanks in advance!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

Hi @Craines ,

 

Please try this measure:

offerDate = 
CALCULATE(
    MIN(StudentTable[ChangeDate]),
    CONTAINSSTRING(StudentTable[Status], "O"),
    ALLEXCEPT(StudentTable, StudentTable[StudentId])
)

 

This gives me the following ouput:

BA_Pete_0-1610557225986.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi @Craines ,

 

Please try this measure:

offerDate = 
CALCULATE(
    MIN(StudentTable[ChangeDate]),
    CONTAINSSTRING(StudentTable[Status], "O"),
    ALLEXCEPT(StudentTable, StudentTable[StudentId])
)

 

This gives me the following ouput:

BA_Pete_0-1610557225986.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




This worked perfectly, thanks @BA_Pete !

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.