Forum Discussion

JCortez's avatar
JCortez
Frequent Visitor
5 days ago
Solved

Clients in a certain program

  Hello, I am looking to create dax code to pull clients enrolled in Program3 only. They might be enrolled in Program1 or Programs2 at the same time, but I only want to see clients who are in progr...
Need Help
  • grazitti_sapna's avatar
    grazitti_sapna
    4 days ago

    Hi JCortez​,

    You can use below dax

    Program3_Only =
VAR CurrentClient = Client_program[ClientID]
VAR HasProgram3 =
    CALCULATE(
        COUNTROWS(Client_program),
        FILTER(
            ALL(Client_program),
            Client_program[ClientID] = CurrentClient
                && RELATED(Program_Level[prle_listname]) = "Program3"
        )
    )
VAR HasOtherPrograms =
    CALCULATE(
        COUNTROWS(Client_program),
        FILTER(
            ALL(Client_program),
            Client_program[ClientID] = CurrentClient
                && RELATED(Program_Level[prle_listname]) <> "Program3"
        )
    )
RETURN
    IF(HasProgram3 > 0 && HasOtherPrograms = 0, 1, 0)

    If you want to check it with date range, use below DAX to create a measure

    Program3_Only_In_Range =
VAR RangeStart = MIN('Date'[Date])
VAR RangeEnd = MAX('Date'[Date])

VAR CurrentClient =
    SELECTEDVALUE(Client[ClientID])

VAR HasProgram3InRange =
    CALCULATE(
        COUNTROWS(Client_program),
        REMOVEFILTERS('Date'),
        FILTER(
            ALL(Client_program),
            Client_program[ClientID] = CurrentClient
                && RELATED(Program_Level[prle_listname]) = "Program3"
                && Client_program[start_date] <= RangeEnd
                && (
                    ISBLANK(Client_program[end_date])
                    || Client_program[end_date] >= RangeStart
                )
        )
    )

VAR HasOtherPrograms =
    CALCULATE(
        COUNTROWS(Client_program),
        REMOVEFILTERS('Date'),
        FILTER(
            ALL(Client_program),
            Client_program[ClientID] = CurrentClient
                && RELATED(Program_Level[prle_listname]) <> "Program3"
        )
    )

RETURN
    IF(HasProgram3InRange > 0 && HasOtherPrograms = 0, 1, 0)

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUserand Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

jubinsoni's avatar
jubinsoni
Icon for Resolver II rankResolver II
4 days ago

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.