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:

link to pbix

"DAX " "DAX help" 'Need help'

Anonymous

1 year ago

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.

CountsBasedOn2Facts.pbix221 KB

13 Replies

SachinNandanwar
Impactful Individual
1 year ago
What about the other personid's apart from 1,10,13 who have also Completed the program ?
- SrMatto
  Helper I
  1 year ago
  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)
VahidDM
Super User
1 year ago
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
- SrMatto
  Helper I
  1 year ago
  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.
  - Anonymous
    Not applicable
    1 year ago
    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.
    CountsBasedOn2Facts.pbix218 KB