Forum Discussion

heatherkw's avatar
heatherkw
Icon for Helper I rankHelper I
3 years ago
Solved

Find Next Enrollment for Client Based on Certain Conditions

Hello, I am still pretty new to DAX and I have looked around for solutions, but they are not quite the same as my scenario, or I'm just not well-versed enough in DAX to understand how to make it work...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi heatherkw ,

    According to your description, I enriched my sample.

    I create four scenarios.

    1.The next Enrollment is the same PersonalID and ProgramType is Emergency Shelter and within 12 months.

    2.The next Enrollment is the same PersonalID and ProgramType isn't Emergency Shelter or Transitional Housing and within 12 months.

    3.The next Enrollment isn't the same PersonalID and ProgramType is Emergency Shelter and within 12 months.

    4.The next Enrollment is the same PersonalID and ProgramType is Transitional Housing and within 12 months.

     

    I modify the formula:

    Column =
IF (
    [ExitType] = "Permanent",
    IF (
        MAXX (
            FILTER (
                'Table',
                'Table'[EnrollmentID]
                    = EARLIER ( 'Table'[EnrollmentID] ) + 1
            ),
            'Table'[ProgramType]
        )
            IN { "Emergency Shelter", "Transitional Housing" }
                && DATEDIFF (
                    'Table'[ExitDate],
                    MAXX (
                        FILTER (
                            'Table',
                            'Table'[EnrollmentID]
                                = EARLIER ( 'Table'[EnrollmentID] ) + 1
                        ),
                        'Table'[Enrollment Date]
                    ),
                    MONTH
                ) <= 12
                && 'Table'[PersonalID]
                    = MAXX (
                        FILTER (
                            'Table',
                            'Table'[EnrollmentID]
                                = EARLIER ( 'Table'[EnrollmentID] ) + 1
                        ),
                        'Table'[PersonalID]
                    ),
        1,
        0
    )
)

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi heatherkw ,

According to your description, here's my solution.

Create a calculated column.

Column =
IF (
    [ExitType] = "Permanent",
    IF (
        MAXX (
            FILTER (
                'Table',
                'Table'[EnrollmentID]
                    = EARLIER ( 'Table'[EnrollmentID] ) + 1
            ),
            'Table'[ProgramType]
        ) = "Emergency Shelter"
            && DATEDIFF (
                'Table'[ExitDate],
                MAXX (
                    FILTER (
                        'Table',
                        'Table'[EnrollmentID]
                            = EARLIER ( 'Table'[EnrollmentID] ) + 1
                    ),
                    'Table'[Enrollment Date]
                ),
                MONTH
            ) <= 12,
        1,
        0
    )
)

Get the result:

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

heatherkw's avatar
heatherkw
Icon for Helper I rankHelper I
3 years ago

I'm also finding that in the real data, this doesn't seem to work. The below records are based on real data. With this calculated field, it returns a 1, but it should return a 0. The person exited to a Permanent destination on both 1/4/2021 and 1/9/2021, and they don't have any enrollment entry dates after 1/9/2021 in any program after that, so it should return a 0. 

EnrollmentIDPersonalIDEntryDateProgramTypeExitDateExitTypeReturn
123488885/28/2020Coordinated Assessment1/4/2021Permanent0
123588886/10/2020PH - Rapid Re-Housing1/9/2021Permanent0
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi heatherkw ,

    According to your description, I enriched my sample.

    I create four scenarios.

    1.The next Enrollment is the same PersonalID and ProgramType is Emergency Shelter and within 12 months.

    2.The next Enrollment is the same PersonalID and ProgramType isn't Emergency Shelter or Transitional Housing and within 12 months.

    3.The next Enrollment isn't the same PersonalID and ProgramType is Emergency Shelter and within 12 months.

    4.The next Enrollment is the same PersonalID and ProgramType is Transitional Housing and within 12 months.

     

    I modify the formula:

    Column =
IF (
    [ExitType] = "Permanent",
    IF (
        MAXX (
            FILTER (
                'Table',
                'Table'[EnrollmentID]
                    = EARLIER ( 'Table'[EnrollmentID] ) + 1
            ),
            'Table'[ProgramType]
        )
            IN { "Emergency Shelter", "Transitional Housing" }
                && DATEDIFF (
                    'Table'[ExitDate],
                    MAXX (
                        FILTER (
                            'Table',
                            'Table'[EnrollmentID]
                                = EARLIER ( 'Table'[EnrollmentID] ) + 1
                        ),
                        'Table'[Enrollment Date]
                    ),
                    MONTH
                ) <= 12
                && 'Table'[PersonalID]
                    = MAXX (
                        FILTER (
                            'Table',
                            'Table'[EnrollmentID]
                                = EARLIER ( 'Table'[EnrollmentID] ) + 1
                        ),
                        'Table'[PersonalID]
                    ),
        1,
        0
    )
)

    Get the result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • heatherkw's avatar
      heatherkw
      Icon for Helper I rankHelper I
      3 years ago

      Hello, someone marked this as a solution, but just letting you know it doesn't work in the real data. It's not returning the correct information at all when we have hundreds of thousands of records. 

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi heatherkw ,

        Could you please provide a sample, then I would know where it stuck and how to dig.

         

        Best Regards,
        Community Support Team _ kalyj