Forum Discussion

Joacb761's avatar
Joacb761
Frequent Visitor
5 years ago
Solved

Count Client IDs enrolled in one program but not in others using two tables.

Hey all!   I'm still in learning DAX and I'm trying to calculate the number of  people who are older than 20, currently enrolled in one program with no concurrent open enrollments in any other prog...
  • daxer-almighty's avatar
    daxer-almighty
    5 years ago

     

    # Enrolled in One Only = 
CALCULATE(
    SUMX(
        DISTINCT( Client[Client ID] ),
        // Check if they're enrolled in one
        // program out of the visible ones
        // and not enrolled in any others,
        // including the ones which are
        // not visible.
        var EnrolledInOnlyOneOutOfVisiblePrograms =
            CALCULATE(
                COUNTROWS( Stats ) = 1,
                ISBLANK( Stats[End Date] ),
                ALL( Stats[Start Date] )
            )
        var EnrolledInOnlyOneOutOfAllPrograms =
            CALCULATE(
                COUNTROWS( Stats ) = 1,
                ALLEXCEPT( Stats, Client ),
                ISBLANK( Stats[End Date] )
            )
        var Result = TRUE()
            && EnrolledInOnlyOneOutOfVisiblePrograms
            && EnrolledInOnlyOneOutOfAllPrograms
        return
            If( Result, 1 )
    ),
    KEEPFILTERS( Client[Age] > 20 )
)

     

daxer-almighty's avatar
daxer-almighty
Icon for Solution Sage rankSolution Sage
5 years ago

 

# Enrolled in One Only = 
CALCULATE(
    SUMX(
        DISTINCT( Client[Client ID] ),
        // Check if they're enrolled in one
        // program out of the visible ones
        // and not enrolled in any others,
        // including the ones which are
        // not visible.
        var EnrolledInOnlyOneOutOfVisiblePrograms =
            CALCULATE(
                COUNTROWS( Stats ) = 1,
                ISBLANK( Stats[End Date] ),
                ALL( Stats[Start Date] )
            )
        var EnrolledInOnlyOneOutOfAllPrograms =
            CALCULATE(
                COUNTROWS( Stats ) = 1,
                ALLEXCEPT( Stats, Client ),
                ISBLANK( Stats[End Date] )
            )
        var Result = TRUE()
            && EnrolledInOnlyOneOutOfVisiblePrograms
            && EnrolledInOnlyOneOutOfAllPrograms
        return
            If( Result, 1 )
    ),
    KEEPFILTERS( Client[Age] > 20 )
)