Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 ID | Age |
1001 | 25 |
1002 | 25 |
1003 | 25 |
1004 | 23 |
1005 | 23 |
1006 | 17 |
1007 | 23 |
Table A: Program Enrollments
Client ID | Program enrollment ID | Program Name | Start Date | End Date |
1001 | PEID-234987 | Program A | 1/1/2021 | 2/1/2021 |
1001 | PEID-124123 | Program B | 1/1/2021 | |
1002 | PEID-291835 | Program A | 1/1/2021 | 2/1/2021 |
1002 | PEID-872134 | Program B | 1/1/2021 | 4/1/2021 |
1003 | PEID-987655 | Program B | 2/1/2021 | |
1004 | PEID-123343 | Program A | 1/1/2021 | |
1004 | PEID-142414 | Program B | 1/1/2021 | |
1005 | PEID-482746 | Program A | 1/1/2021 | 2/1/2021 |
1005 | PEID-868686 | Program B | 2/1/2021 | |
1005 | PEID-484842 | Program C | 1/1/2021 | 2/1/2021 |
1006 | PEID-712350 | Program B | 1/1/2021 | |
1007 | PEID-643149 | Program A | 1/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
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.
Solved! Go to Solution.
# 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 )
)
# 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 )
)
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |