Forum Discussion
Clients in a certain program
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!
Super UserHi 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
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
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!This worked, thank you so much.
