Forum Discussion

SrMatto's avatar
SrMatto
Icon for Helper I rankHelper I
1 year ago
Solved

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...
"DAX " "DAX help" 'Need help'
  • Anonymous's avatar
    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.

VahidDM's avatar
VahidDM
Icon for Super User rankSuper 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's avatar
SrMatto
Icon for Helper I rankHelper 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's avatar
    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.

    • SrMatto's avatar
      SrMatto
      Icon for Helper I rankHelper I
      1 year ago

      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:

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    1 year ago

    You right, it should be SELECTEDVALUE 🙂