- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculating Completed Programs for Enrolled Persons on or after filter Dates
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:
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 selectedIdDate
.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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You right, it should be SELECTEDVALUE 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-zhengdxu-msft ,
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-zhengdxu-msft ,
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What about the other personid's apart from 1,10,13 who have also Completed the program ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
08-05-2024 07:36 AM | |||
10-05-2023 07:01 PM | |||
09-12-2024 12:22 PM | |||
10-09-2024 06:29 AM | |||
07-15-2024 08:37 AM |
User | Count |
---|---|
23 | |
12 | |
10 | |
10 | |
8 |
User | Count |
---|---|
16 | |
15 | |
15 | |
12 | |
10 |