Forum Discussion
SrMatto
Helper I
1 year agoCalculating 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...
- Anonymous1 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.
Anonymous
1 year agoNot applicable
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
Helper I
1 year agoThank you so much Anonymous !