Calculate percent of distinct clients that experienced their first enrollment during a timeframe

[heatherkw]

I have been trying to make this work for hours and there is I think just one step somewhere that I seem to be missing. In our data, we have enrollments, clients, and households - households have multiple clients, and clients have multiple enrollments. We have unique ids at all of these levels. I am trying to display in a bar graph the percent of enrollments, clients, and households that experienced an enrollment for the first time during whatever time frame the user chooses using a slicer. Right now, the bar graph works perfectly when I look at the enrollment level, but then things get weird when I look at the client and household levels.

I used this to calculate the client's first enrollment date:

FirstEnrollmentCl = CALCULATE (

MIN('Enrollment'[EnrollmentEntryDate]),

ALLEXCEPT ('Enrollment','Enrollment'[PersonalID]))

Then I used this to return the percentages for the bar graph:

FirstEnrollClSelect = IF('Enrollment'[FirstEnrollmentCl]= 'Enrollment'[EnrollmentEntryDate], "First Enrollment","Not First Enrollment")

So again, at the enrollment level, this works beautifully, but what is happening at the client level is that a client with more than one enrollment is being counted twice, once in the first enrollment count, and again in the not first enrollment count. So I need a way to determine the MAX value essentially for the client during the timeframe based on a slicer - if they had a first enrollment during the timeframe chosen at all, then they would be counted in the first enrollment category, otherwise in the not first enrollment category. Totals should equal the distinct count of clients.

Here is sample data and what I need the results to be like:

Enrollment	Client	Household	EnrollmentDate	FirstEnrollmentClient	FirstEnrollmentHousehold
100002	1235	1235	1/1/2020	First Enrollment	First Enrollment
100000	1234	1234	2/1/2020	First Enrollment	First Enrollment
100003	1236	1236	5/1/2020	First Enrollment	First Enrollment
100004	1237	1236	5/1/2020	First Enrollment	First Enrollment
100005	1238	1236	5/1/2020	First Enrollment	First Enrollment
100001	1234	1234	8/1/2020	Not First Enrollment	Not First Enrollment
100006	1236	1236	10/4/2021	Not First Enrollment	Not First Enrollment
100007	1237	1236	10/4/2021	Not First Enrollment	Not First Enrollment
100008	1238	1236	10/4/2021	Not First Enrollment	Not First Enrollment

If I'm looking at enrollments between say 5/1/2020 and 10/31/2021, the results would need to be:

7 distinct enrollments, 3 first time (43%)

4 distinct clients, 3 had first time enrollments (75%)

2 distinct households, 1 had a first time enrollment (50%)

I think I've been staring at this for too long and my mind has gone all over the place. I'm sure I'm missing something obvious. 

Thanks!

[v-yinliw-msft · Accepted solution]

Hi @heatherkw ,

You can try this method:

Measure:

Ques 1 Total =
CALCULATE (
    COUNT ( 'Sample Table'[Client] ),
    FILTER (
        'Sample Table',
        MONTH ( 'Sample Table'[EnrollmentDate] ) < 11
            && MONTH ( 'Sample Table'[EnrollmentDate] ) > 4
    )
)

Ques 1 First En =
CALCULATE (
    COUNT ( 'Sample Table'[Client] ),
    FILTER (
        'Sample Table',
        MONTH ( 'Sample Table'[EnrollmentDate] ) < 11
            && MONTH ( 'Sample Table'[EnrollmentDate] ) > 4
            && 'Sample Table'[FirstEnrollmentClient] = "First Enrollment"
    )
)

Ques 1 per =
VAR _divide =
    DIVIDE ( [Ques 1 First En], [Ques 1 Total] )
RETURN
    FORMAT ( _divide, "Percent" )

Ques 2 Total =
CALCULATE (
    DISTINCTCOUNT ( 'Sample Table'[Client] ),
    FILTER (
        'Sample Table',
        MONTH ( 'Sample Table'[EnrollmentDate] ) < 11
            && MONTH ( 'Sample Table'[EnrollmentDate] ) > 4
    )
)

Ques 2 First En =
CALCULATE (
    COUNT ( 'Sample Table'[FirstEnrollmentClient] ),
    FILTER (
        'Sample Table',
        MONTH ( 'Sample Table'[EnrollmentDate] ) < 11
            && MONTH ( 'Sample Table'[EnrollmentDate] ) > 4
            && 'Sample Table'[FirstEnrollmentClient] = "First Enrollment"
    )
)

Ques 2 per =
VAR _divide2 =
    DIVIDE ( [Ques 2 First En], [Ques 2 Total] )
RETURN
    FORMAT ( _divide2, "Percent" )

Ques 3 Total =
CALCULATE (
    DISTINCTCOUNT ( 'Sample Table'[Household] ),
    FILTER (
        'Sample Table',
        MONTH ( 'Sample Table'[EnrollmentDate] ) < 11
            && MONTH ( 'Sample Table'[EnrollmentDate] ) > 4
    )
)

 New Table:

Ques 3 table =
SUMMARIZE (
    'Sample Table',
    'Sample Table'[Household],
    'Sample Table'[EnrollmentDate],
    'Sample Table'[FirstEnrollmentHousehold]
)

New Measure:

Ques 3 First En =
CALCULATE (
    COUNT ( 'Ques 3 table'[Household] ),
    FILTER (
        'Ques 3 table',
        MONTH ( 'Ques 3 table'[EnrollmentDate] ) < 11
            && MONTH ( 'Ques 3 table'[EnrollmentDate] ) > 4
            && 'Ques 3 table'[FirstEnrollmentHousehold] = "First Enrollment"
    )
)

Ques 3 per =
VAR _divide3 =
    DIVIDE ( [Ques 3 First En], [Ques 3 Total] )
RETURN
    FORMAT ( _divide3, "Percent" )

Is it what you expect?

Hope this helps you.

Best Regards,

Community Support Team _Yinliw

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

[heatherkw]

Hi there, thanks for this. This still doesn't get me what I need unfortunately. I need to be able to show a bar graph that has the percent where first enrollment = yes AND the percent where first enrollment = NO. Since clients have multiple enrollments, the calculations I have tried have duplicated the client count for NO since they also have enrollments that aren't their first ones. Someone else also tried to help and their suggestions were very similar to this and didn't work the way I needed. 😞