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!

Shai_Karmani's avatar
Shai_Karmani
Icon for Super User rankSuper User
5 days ago

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

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

    • JCortez's avatar
      JCortez
      Frequent Visitor
      4 days ago

      This worked, thank you so much.

    • JCortez's avatar
      JCortez
      Frequent Visitor
      4 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.