topic Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates in DAX Commands and Tips

Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Sat, 16 Nov 2024 18:28:10 GMT

Hi
I'm trying to achieve the following calculation:

"Count of all the program that are completed on or after some enrollment date".

In the context of this data model:

For all the persons enrolled on one or more IdDate, count all FactCompletion[Program] that have a FactCompletion[degree of completion]="Complete", on or after the selected enrollment dates

Note that the data model structure with the bridge table may not be changed (other Facts have been removed).

For example, for semeter 2 of 2020 and 2021, the enrolled are:

For these PersonId, the completion on or after is:

Then, the expected result is:

link to pbix

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SachinNandanwar — Sat, 16 Nov 2024 19:16:52 GMT

What about the other personid's apart from 1,10,13 who have also Completed the program ?

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Sat, 16 Nov 2024 19:37:27 GMT

in the example, only the ones that have enrollment in the selected dateId must be considered for the calculation. (2,6,16,17 don't count as complete)

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

VahidDM — Sun, 17 Nov 2024 07:01:07 GMT

Hi SrMatto

To calculate the count of programs that are completed on or after the enrollment dates for persons enrolled on selected dates, you can use the following DAX measure:

CompletedPrograms = SUMX( SUMMARIZE( FILTER( FactEnrollment, FactEnrollment[IdDate] IN SELECTEDVALUES(FactEnrollment[IdDate]) ), FactEnrollment[PersonId], FactEnrollment[IdDate] ), VAR CurrentPerson = FactEnrollment[PersonId] VAR EnrollmentDate = FactEnrollment[IdDate] RETURN CALCULATE( DISTINCTCOUNT(FactCompletion[Program]), FactCompletion[PersonId] = CurrentPerson, FactCompletion[Degree of completion] = "Complete", FactCompletion[CompletionDate] >= EnrollmentDate ) )

Explanation:

SUMMARIZE creates a table of enrolled persons and their enrollment dates based on the selected IdDate.
SUMX iterates over each person and enrollment date.
CALCULATE counts the distinct programs where:
- The PersonId matches.
- Degree of completion is "Complete".
- CompletionDate is on or after the EnrollmentDate.
The measure sums up the counts to provide the total number of completed programs for the enrolled persons.

Notes:

Ensure your table names (FactEnrollment, FactCompletion) and column names match those in your data model.
This measure respects the selected enrollment dates and filters the completions accordingly.
No changes to your data model are required.

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

Appreciate your Kudos!!

LinkedIn|Twitter|Blog |YouTube

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Sun, 17 Nov 2024 21:48:31 GMT

Hi VahidDm,
Thanks, but it doesn't work. For start, SELECTEDVALUES doesn't exist in DAX. I changed it to VALUES to get the desired result, though.
Also, CompletionDate is not part of the data model. (Replaced it with Factcompletion[idDate])
Still, even with this correction, calculation doesn't gives the correct answer.

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

Anonymous — Mon, 18 Nov 2024 06:29:55 GMT

Hi SrMatto

Please try this:

Here I create a measure:

Count = VAR _values = SUMMARIZE ( ALLSELECTED ( FactEnrollment ), 'FactEnrollment'[Person ID] ) VAR _vtable = FILTER ( ALL ( FactCompletion ), 'FactCompletion'[Person ID] IN _values && 'FactCompletion'[Degree of Completion] = "Complete" ) RETURN IF ( ISINSCOPE ( FactCompletion[Program] ), COUNTROWS ( FILTER ( _vtable, [Program] = SELECTEDVALUE ( 'FactCompletion'[Program] ) ) ), COUNTROWS ( _vtable ) )

Then drag it to the table visual to replace the original [Count] field so that the result is as follow:

The PBIX file is attached.

Best Regards

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

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Mon, 18 Nov 2024 15:46:53 GMT

Thank you Zhengdong Xu,

It is very close, but unfortunately, does not generalize.

For instance, in year 2022 semester 1 (20221) yours return 2, but doing it manually gives 1:

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

Anonymous — Tue, 19 Nov 2024 05:57:57 GMT

Hi SrMatto

I'm a little confused. If possible, please tell me about the logic.

Here's how I understand it:
___________________________________________________________________________________________

In the first case, the 2 of the sport is made up of 20212 and 20232.

In the logic, we can get 5,7,8,10,13,15 from FactEnrollment table:

Then filter these ID in the FactCompletion table, so that the result is 1(Craftsman)+2(Sport)

Same to the second case, the result should be 2.

___________________________________________________________________________________________

If there is something I‘ve misunderstood, please point it out.

Best Regards

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

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Tue, 19 Nov 2024 16:09:00 GMT

Hi Anonymous ,
Thank you for the follow up.
To clarify: I think you might be missing the condition "on or after DateID"

The first example is correct.
The second example, result should be 1 because personID 13 has completed on 2021 which is PRIOR to the IdDate filter (set as 2022, semester 1)

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

Anonymous — Wed, 20 Nov 2024 06:59:54 GMT

Hi SrMatto

Please try this measure:

Count = VAR _Slicer = MIN ( 'DimDate'[IdDate] ) VAR _values = SUMMARIZE ( ALLSELECTED ( FactEnrollment ), 'FactEnrollment'[Person ID] ) VAR _vtable = FILTER ( ALL ( FactCompletion ), 'FactCompletion'[Person ID] IN _values && 'FactCompletion'[Degree of Completion] = "Complete" && 'FactCompletion'[IdDate] >= _Slicer ) RETURN IF ( ISINSCOPE ( FactCompletion[Program] ), COUNTROWS ( FILTER ( _vtable, [Program] = SELECTEDVALUE ( 'FactCompletion'[Program] ) ) ), COUNTROWS ( _vtable ) )

The result is as follow:

Best Regards

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

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Wed, 20 Nov 2024 12:52:11 GMT

Hi Anonymous ,
It's very cool. Thanks.
I works almost perfectly!
Still, I found an glitch. How would you solve the fact that: if the Program is not present in the Enrollment YearSemester, it don't show up?
Look:
For IdDate=20202:

Yet, your (almost perfect!) calculation returns:

Which I believe is because "Sport" don't appear in 20202

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

Anonymous — Thu, 21 Nov 2024 06:21:08 GMT

Hi SrMatto

Please try this:

Create a new table:

Table = VALUES(FactCompletion[Program])

Then change the 'FactCompletion'[Program] into 'Table'[Program] in the table visual:

Then I did some change on the [Count] measure:

Count = VAR _Slicer = MIN ( 'DimDate'[IdDate] ) VAR _values = SUMMARIZE ( ALLSELECTED ( FactEnrollment ), 'FactEnrollment'[Person ID] ) VAR _vtable = FILTER ( ALL ( FactCompletion ), 'FactCompletion'[Person ID] IN _values && 'FactCompletion'[Degree of Completion] = "Complete" && 'FactCompletion'[IdDate] >= _Slicer ) RETURN IF ( ISINSCOPE ( 'Table'[Program] ), COUNTROWS ( FILTER ( _vtable, [Program] = SELECTEDVALUE ( 'Table'[Program] ) ) ), COUNTROWS ( _vtable ) )

The result is as follow:

Best Regards

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

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

SrMatto — Thu, 21 Nov 2024 21:14:50 GMT

Thank you so much Anonymous !

Re: Calculating Completed Programs for Enrolled Persons on or after filter Dates

VahidDM — Thu, 21 Nov 2024 23:56:36 GMT

You right, it should be SELECTEDVALUE 🙂