Forum Discussion

JCortez's avatar
JCortez
Frequent Visitor
11 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
    11 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!

JCortez's avatar
JCortez
Frequent Visitor
11 days ago

Hello Shai,

I got it to show 1 in the new column when program3 is detected but it is also showing the clients that are enrolled in other programs at the same time. So if client John Doe is enrolled in Program3 and Program2 at the same time I don't want them shown. Only if they are in Program3 alone.

Also can I add a date range to detect a client enrolled in multiple programs but enrolled in Program3 during a date range? Hope that makes sense.

Thank you,
Javier

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
11 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!

  • JCortez's avatar
    JCortez
    Frequent Visitor
    11 days ago

    This worked, thank you so much.

  • JCortez's avatar
    JCortez
    Frequent Visitor
    11 days ago

    When using the in date range code, I am getting an error on this line:

    REMOVEFILTERS('Date'),

    Should these be pointing to client_program.start_date and client_program.end_date ?

    I created the measure in the client_program table. How do I use it? Can I create a slicer and use this to filter by date range?

    Thank you.