Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Joacb761
Frequent Visitor

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 program using two tables. Current enrollment is determined by the End Date in Table 2 being blank, and not current enrollment is determined by having an end date or not having a row for that program type. Any help is appreciated!

 

Here's an example of how the data is set up:

Table A: Client demographics

 

 client IDAge
100125
100225
100325
100423
100523
100617
100723

 

Table A: Program Enrollments

Client IDProgram  enrollment IDProgram NameStart DateEnd Date
1001PEID-234987Program A1/1/20212/1/2021
1001PEID-124123Program B1/1/2021 
1002PEID-291835Program A1/1/20212/1/2021
1002PEID-872134Program B1/1/20214/1/2021
1003PEID-987655Program B2/1/2021 
1004PEID-123343Program A1/1/2021 
1004PEID-142414Program B1/1/2021 
1005PEID-482746Program A1/1/20212/1/2021

1005

PEID-868686Program B2/1/2021 

1005

PEID-484842Program C1/1/20212/1/2021

1006

PEID-712350Program B1/1/2021 

1007

PEID-643149Program A1/1/2021 

 

 

In this example i'd want to know how many clients older than 13 are currently in Program B, and not enrolled in Program A or C.

For this example I'd hope the count to be 3 (client 1001, Client 1003, Client 1005) and exclude

  • Client 2 (no open Program B enrollment)
  • Client 4 (has an open Program A enrollment)
  • Client 6 (Age below 20)
  • Client 7 (does not have a Program B enrollment)

Any and all help would be appreciated! right now I'm just trying to calculate this count and I'm not worried about how to visualize it just yet.

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

daxeralmighty_0-1626529162879.pngdaxeralmighty_1-1626529196692.pngdaxeralmighty_2-1626529237216.png

daxeralmighty_3-1626529290444.pngdaxeralmighty_4-1626529301750.png

daxeralmighty_5-1626529333202.png

 

# 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 )
)

 

View solution in original post

1 REPLY 1
daxer-almighty
Solution Sage
Solution Sage

daxeralmighty_0-1626529162879.pngdaxeralmighty_1-1626529196692.pngdaxeralmighty_2-1626529237216.png

daxeralmighty_3-1626529290444.pngdaxeralmighty_4-1626529301750.png

daxeralmighty_5-1626529333202.png

 

# 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 )
)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors