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.

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

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

Anonymous's avatar
Anonymous
Not applicable
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.

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

    Thank you so much Anonymous !