Clients in a certain program

Hi Javier, for this kind of "only in one group, never in others" filter the cleanest DAX pattern is to count the distinct programs each client has ever been in, then keep only those with exactly one program and that one is Program3.

Assuming the usual relationships (Client -> Client_program -> Program_Level), add a calculated column on your Client table:

Only_Program3 =
VAR _programs =
    CALCULATETABLE( VALUES( Program_Level[prle_listname] ) )
RETURN
    IF(
        COUNTROWS( _programs ) = 1 && "Program3" IN _programs,
        1,
        0
    )

Then build a table visual with Client[listname], Program_Level[prle_listname], Client_program[start_date] and Client_program[end_date], and set a visual-level filter where Only_Program3 = 1. That returns only clients who have ever been in Program3 and in no other program.

If you need the same thing driven by a slicer (so the user picks the program name), convert it to a measure using HASONEVALUE on the client in context plus a VAR holding the selected program value.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,

Shai Karmani

Let's connect in LinkedIn

Hi JCortez, I think the reason Program3 clients who also have Program2 still show up is that the program name in your visual is filtering the count, so each row only sees its own program and the measure cannot tell the client is enrolled elsewhere. The fix is to count programs per client while ignoring the program filter. Something like Program3 Only = VAR ProgramsPerClient = CALCULATE ( DISTINCTCOUNT ( Client_program[prle_id] ), REMOVEFILTERS ( Program_Level ) ) VAR InProgram3 = CALCULATE ( COUNTROWS ( Client_program ), Program_Level[prle_listname] = "Program3" ) RETURN IF ( ProgramsPerClient = 1 && InProgram3 > 0, 1, 0 ) and then use it as a visual level filter equal to 1. Please adjust the column names to match your model, because I am guessing the program key. For the date range, I would add a Date table or a pair of date slicers and test overlap, so the client counts when the Program3 enrollment touches the selected window. Program3 In Range = VAR s = MIN ( 'Date'[Date] ) VAR e = MAX ( 'Date'[Date] ) RETURN CALCULATE ( COUNTROWS ( Client_program ), Program_Level[prle_listname] = "Program3", FILTER ( Client_program, Client_program[start_date] <= e && ( ISBLANK ( Client_program[end_date] ) || Client_program[end_date] >= s ) ) ). The open ended check matters because current enrollments usually have no end date. If you want both rules together, which means Program3 only and enrolled during the window, put the two measures as separate visual filters, both equal to 1. So the logic is program count per client -> remove the program filter first, enrollment in range -> overlap test on start and end dates, and final list -> both measures filtered to 1. So I would first validate with one client such as John Doe, since that quickly shows whether the program filter is being removed correctly.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

You can create a calculated table in Power BI that returns clients enrolled in Program3 only, excluding anyone who has ever enrolled in another program.

Assuming Client_program links clients to programs, try:

Program3_Only =

FILTER (

ADDCOLUMNS (

SUMMARIZE (

FILTER (

Client_program,

RELATED ( Program_Level[prle_listname] ) = "Program3"

),

Client_program[Client_ID],

Client_program[start_date],

Client_program[end_date]

),

"Client Name", RELATED ( Client[listname] ),

"Program Name", "Program3"

),

CALCULATE (

COUNTROWS ( Client_program ),

ALLEXCEPT ( Client_program, Client_program[Client_ID] ),

Client_program[Program_ID] <>

LOOKUPVALUE (

Program_Level[Program_ID],

Program_Level[prle_listname], "Program3"

)

) = 0

)

Note: Replace Client_ID and Program_ID with your actual key column names. This assumes relationships are configured correctly between the three tables.

If you share the key columns in Client_program that link to Client and Program_Level, I can provide the exact DAX for your model