cancel
Showing results for
Did you mean:

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

Helper II

## Select specific date based on filter criteria

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

StudentTable

 StudentId Status ChangeDate Deferral? AppDate Student1 A1 01/10/2020 TRUE 01/10/2020 Student1 Os2 05/10/2020 TRUE 01/10/2020 Student1 Oz5 06/10/2020 TRUE 01/10/2020 Student2 Aa5 07/10/2020 FALSE Student2 R33 11/10/2020 FALSE Student3 A7h 02/10/2020 TRUE 02/10/2020 Student3 O1 04/10/2020 TRUE 02/10/2020 Student3 AC8 05/10/2020 TRUE 02/10/2020 Student4 A1 06/10/2020 TRUE 06/10/2020 Student4 O3 09/10/2020 TRUE 06/10/2020 Student4 O3 10/10/2020 TRUE 06/10/2020

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

 Status Application Offer Acceptance A1 1 0 0 Aa5 1 0 0 A7h 1 0 0 Os2 1 1 0 Oz5 1 1 0 O1 1 1 0 O3 1 1 0 AC8 1 1 1 R33 1 0 0

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:
 StudentId Status ChangeDate Deferral? AppDate OfferDate Student1 A1 01/10/2020 TRUE 01/10/2020 05/10/2020 Student1 Os2 05/10/2020 TRUE 01/10/2020 05/10/2020 Student1 Oz5 06/10/2020 TRUE 01/10/2020 05/10/2020 Student2 Aa5 07/10/2020 FALSE Student2 R33 11/10/2020 FALSE Student3 A7h 02/10/2020 TRUE 02/10/2020 04/10/2020 Student3 O1 04/10/2020 TRUE 02/10/2020 04/10/2020 Student3 AC8 05/10/2020 TRUE 02/10/2020 04/10/2020 Student4 A1 06/10/2020 TRUE 06/10/2020 09/10/2020 Student4 O3 09/10/2020 TRUE 06/10/2020 09/10/2020 Student4 O3 10/10/2020 TRUE 06/10/2020 09/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?

1 ACCEPTED SOLUTION
Super User

Hi @Craines ,

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

This gives me the following ouput:

Pete

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

Proud to be a Datanaut!

2 REPLIES 2
Super User

Hi @Craines ,

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

This gives me the following ouput:

Pete

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

Proud to be a Datanaut!

Helper II

This worked perfectly, thanks @BA_Pete !

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors