Dax Measures/Columns for assigning a list of names to groups with criteria

[RBTuesday]

I have a table (in excel) that consists of a list of names, a Y/N flag for whether the person is a wheelchair user or fall risk, and Y/N flags as to whether they receive services on specific days of the week. Once the table is pulled into PBI, it converts those Y/N to 1/0 (The Y/N is more straightforward for humans to parse, so we use those in the excel source file). 

I am trying to coax PBI into taking the data source and spitting out group assignments for each day of the week. The criteria for the groups is: no more than one wheelchair user per group, and no more than one fall risk per group. (It's okay for a group to have both a wheelchair user and a fall risk). The group maximum size is 3. 

I have gotten as far as having PBI distinguish if someone is present or not present; and if they are eligible for group services or must receive one-on-one service. When it comes to breaking them out into groups, something has gone slightly awry. It's putting the wheelchair users into groups first (I haven't even started trying to work in the fall risk condition yet). But when it comes to filling the groups out the rest of the way, the sorting falls apart. I can't get it to stick to the correct group size. 

In PBI, after Query converts the Y/N to 1/0, the data set is then broken down by the PREGROUP calculated column to pull people eligible for the group services:

PREGROUP = IF(TBL_DATA[Monday]=1,IF(TBL_DATA[Baseline]=1,"RATIO 1:1","RATIO 2:3"),"NOT PRESENT")

Then in another calculated column, this is the code that I have so far for pushing the group-service-eligible people into their groups on Monday (I'm intending to have a separate tab for each day of the week, rather than using slicers, to minimize the need for interaction, so I'm building the day selection into the DAX codes):

Group Assignment = 
VAR GroupSize = 3

VAR WheelchairRank = 
    IF(
        TBL_DATA[Wheelchair] = 1,
        RANKX(FILTER(TBL_DATA, TBL_DATA[Wheelchair] = 1), TBL_DATA[Client Name], , ASC, Dense)-1
    )

VAR NonWheelchairRank = 
    IF(
        TBL_DATA[Wheelchair] = 0,
        RANKX(FILTER(TBL_DATA, TBL_DATA[Wheelchair] = 0), TBL_DATA[Client Name], , ASC, Dense)
    )

RETURN 
    IF(TBL_DATA[Monday]=1 && TBL_DATA[PREGROUP]="RATIO 2:3",
    IF(TBL_DATA[Wheelchair]=1,"Group "&WheelchairRank,"Group "&ROUNDDOWN((NonWheelchairRank / GroupSize),0))
    ,"")
   

This is the output I'm getting in the matrix. The pregroup is sorting people fine, but the group assignments aren't adhering to the group size of 3.  I had to tack the -1 on in the WheelchairRank variable to make put the first wheelchair user in the first group instead of starting with a random group of 2 people, and I've tried playing with -1 / +1 at the end of other lines with worse results on the grouping. The current state is closest with only one group of 4 existing. 

I've played around a bit with inserting <= checks via IF, but I couldn't get that to work right either. I'm fairly new to using PBI and teaching myself on the fly, and this has surpassed by understanding and ability to troubleshoot. Any suggestions would be greatly appreciated!

If you can also point me towards how to loop in the second criteria (Fall Risk) that would also be amazing. 

This is (a fake version of) the data I am working with. (Baseline 3 indicates they are allowed to receive group service, 1 indicates they cannot.)

Client Name	Baseline	Wheelchair	Fall Risk	Monday	Tuesday	Wednesday	Thursday	Friday
Leonard Parks	1	N	N	N	Y	N	Y	N
Kim Maldonado	3	Y	N	Y	N	Y	N	Y
Victoria Livingston	1	N	N	Y	N	Y	N	Y
Jesse Levy	3	Y	N	Y	N	Y	N	Y
Omar Green	1	N	Y	Y	N	Y	N	Y
Doug Cobb	3	N	N	Y	N	Y	N	Y
Mark McIntosh	3	N	N	Y	N	Y	N	Y
Leo Atkins	3	N	N	Y	N	Y	N	Y
Barry Cramer	3	N	Y	Y	N	Y	N	Y
Justin Baker	3	N	N	Y	N	Y	N	Y
Trudy Howell	1	N	N	Y	N	Y	N	Y
Daniel Landry	3	N	N	Y	N	Y	N	Y
Alberta Cruz	1	N	N	Y	Y	Y	Y	Y
Heidi Sheppard	3	Y	N	Y	Y	Y	Y	Y
Abel Hand	1	N	N	Y	Y	Y	Y	Y
Miranda Reilly	3	N	N	Y	Y	Y	Y	Y
Ricardo Williams	3	N	N	Y	Y	Y	Y	Y
Natalie Alexander	3	N	N	Y	Y	Y	Y	Y
Alison Fuller	3	Y	N	N	Y	N	Y	N
Christi Willson	3	N	Y	N	Y	N	Y	N

[v-echaithra]

Hi @RBTuesday ,

We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-echaithra]

Hi @RBTuesday ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required, please provide sample pbix file. We are available to support you and are committed to helping you reach a resolution.

Thank you.

[grazitti_sapna]

Hi @RBTuesday,

I'll suggest to do all your gruoping in Power Query instead of creating calculated column with DAX.

I've added an example .pbix file for you, this is just a sample and you can modify it according to your requirements.

Let me know if you face any challanges.

🌟 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 SuperUser and 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!

[RBTuesday]

I tried replacing the generated table with the actual file name, which correctly pulls in the data from the excel source file, and lets me then put the "replace value" steps in that flips Y/N to 1/0. 

let

//==================================================
// SAMPLE DATA (replace with Excel.CurrentWorkbook source)
//==================================================
Source = Excel.Workbook(File.Contents("C:\fullfilenameishere.xlsx"), null, true),
Navigation = Source{[Item="TBL_DATA",Kind="Table"]}[Data],
    #"Replaced Value" = Table.ReplaceValue(Navigation,"Y","1",Replacer.ReplaceText,{"Wheelchair", "Fall Risk", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","0",Replacer.ReplaceText,{"Wheelchair", "Fall Risk", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}),



//==================================================
// FUNCTION TO ASSIGN GROUPS
//==================================================

AssignGroups=(InputTable as table, DayColumn as text)=>

It breaks on the manual "Assign Groups" input step. The InputTable selection box is empty/non selectable.

So the subsecquent steps all error out as:

Not sure where I went wrong as this seemed like a fairly straightfoward replacement, if you can nudge me in the right direction again.

(I am new to PBI (about a month in of trying to teach myself) and haven't used the Advanced Editor M code with any real success thus far. I get along with Dax or the query "add column" functions a lot better. 

As an alternative I played a bit with building out the columns in query instead of dax as you'd suggested, trying to mimic your code's processes. I can get the pregroup coding in easily enough. I tried using a risk ranking system (wheelchair + fall = 4, wheelchair = 3, fall =2, else =1) for priority and then indexing the individual rank groups, but when I try to group from there it's still inconsistent on the number of people it sticks in a group so I have a ways to go here too it seems.)

[v-echaithra]

Hi @RBTuesday ,

Thanks for the clarification. I realized the issue was that I pasted the function code into the main query, so Power Query treated it as a function and showed the parameter input screen instead of returning a table. That explains why the InputTable dropdown was empty and the later steps errored out.

Open Power Query Editor in Power BI.
Go to Home > New Source > Blank Query.
Rename this new query to AssignGroups.
Open Advanced Editor for AssignGroups and paste only the function code starting with:
(InputTable as table, DayColumn as text)=>

let
...
in
Final

Click Done. This query should now show the function parameter screen (this is correct).

Select your existing data query (TBL_DATA).
Open Advanced Editor for TBL_DATA. Keep your source/replace steps, then call the function like this:
Mon = AssignGroups(#"Replaced Value1","Monday"),
Tue = AssignGroups(Mon,"Tuesday"),
Wed = AssignGroups(Tue,"Wednesday"),
Thu = AssignGroups(Wed,"Thursday"),
Fri = AssignGroups(Thu,"Friday")

in
Fri

Click Done and then Close & Apply.

Now AssignGroups is your reusable function, and TBL_DATA is the final table using it.

Hope this helps.

[RBTuesday]

Putting the code into the blank query works, and allows the table name selection:

However, as soon as I click over from the AssignGroups query to the TBL_DATA query to put the "run for all weekdays" portion into the code, the InputTable field empties (it took me a minute to realize it was doing that as I was testing some failure points and happened to flick back and forth before I added the weekdays code into TBL_DATA).

So when I add the weekdays code It gives the following error, presumably because the table reference has gone missing:

Returning to the AssignGroups parameters now, it's both empty and locked.

Is there a way to just hard code TBL_DATA as the table while keeping the flexible DayColumn field? Or to lock it once it's entered on the parameter screen? (I tried replacing all the InputTable references with #"TBL_DATA" as a guess, and while that did not cause an error, it resulted in the same 'Client' not found' error message so clearly that's just nomenclature and not the fuctional process.)

I appreciate all your assistance here. 

[v-echaithra]

Hi @RBTuesday ,

The InputTable parameter going blank is expected behavior and not related to the error. The issue is caused by a column name mismatch in the function during the merge step.

In the AssignGroups function, the Assignments table is created using:
Assignments = Table.FromRecords(Result[Output])

This creates a column named Client, which is then used in the join:
Table.NestedJoin(
InputTable,
{"Client Name"},
Assignments,
{"Client"},
"Join",
JoinKind.LeftOuter)

For this to work correctly, the column defined in NewOutput must match:
Client = person[Client Name]
If your source column is not exactly "Client Name" (for example, it could be Client, ClientName, or contain trailing spaces), the join will fail.

To avoid mismatch, align both sides of the join by using the same column name:
Client Name = person[Client Name]
and update the join to:
Table.NestedJoin(
InputTable,
{"Client Name"},
Assignments,
{"Client Name"},
"Join",
JoinKind.LeftOuter)

Also, avoid hardcoding TBL_DATA inside the function, as it will break the step-by-step chaining logic used across weekdays.

Hope this helps.
Thank you.

[RBTuesday]

I checked the spaces and such and everything looked fine, but knowing how weird PBI can get with code pasted in externally (" from other sources is constantly acting like it's an entirely different character when pasted in to Dax) I copied the words "Client Name" directly from a txt file and pasted them into the column header and code where you suggested to ensure everything was consistently named. This got rid of the "client name not found" type error, so I guess it was indeed being quirky.

Unfortunately it kicked me right back to this error, on each day-of-the-week step:

The two troubleshooting steps I found for that error were checking the data type and looking to see if anything in the sorted column was a list/record. 

I tried putting a type into the fourth argument of the Sorted= function in the AssignGroups code to make it numbers (Int64.Type) in case it was a type mismatch issue, and made sure all the 1/0 columns were also set to whole number.  This changed nothing so I took it back out.

No idea how to test for the list/record thing as everything I've looked at says you should be able to see the column that has been generated and things seem to be getting gummed up before columns are added to the table...

So this is where I am at codewise.

TBL_DATA

let
    Source = Excel.Workbook(File.Contents("C:\Users\RuairaMorgan\Typical Life Corporation\Fiscal - Documents\02. Accounts Receivable\000DATABASEFILES\DAY PROG SCHEDLING PROJECT\DayProgSchedSource.xlsx"), null, true),
    TBL_DATA_Table = Source{[Item="TBL_DATA",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TBL_DATA_Table,{{"Client Name", type text}, {"Baseline", Int64.Type}, {"Wheelchair", type text}, {"Fall Risk", type text}, {"Monday", type text}, {"Tuesday", type text}, {"Wednesday", type text}, {"Thursday", type text}, {"Friday", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Y","1",Replacer.ReplaceText,{"Wheelchair", "Fall Risk", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","N","0",Replacer.ReplaceText,{"Wheelchair", "Fall Risk", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Baseline", Int64.Type}, {"Wheelchair", Int64.Type}, {"Fall Risk", Int64.Type}, {"Monday", Int64.Type}, {"Tuesday", Int64.Type}, {"Wednesday", Int64.Type}, {"Thursday", Int64.Type}, {"Friday", Int64.Type}}),
    Mon=AssignGroups(Source,"Monday"),
    Tue=AssignGroups(Mon,"Tuesday"),
    Wed=AssignGroups(Tue,"Wednesday"),
    Thu=AssignGroups(Wed,"Thursday"),
    Fri=AssignGroups(Thu,"Friday")
in
    Fri

AssignGroups

(InputTable as table, DayColumn as text)=>

let

PresentOnly=
Table.SelectRows(
InputTable,
each Record.Field(_,DayColumn)=1 and [Baseline]=3
),

OneToOne=
Table.SelectRows(
InputTable,
each Record.Field(_,DayColumn)=1 and [Baseline]=1
),

Sorted=
Table.Sort(
Table.AddColumn(
PresentOnly,
"Priority",
each
[Wheelchair]*2+
[Fall Risk]
),
{{"Priority",Order.Descending},{"Client Name",Order.Ascending}}
),

Rows=
Table.ToRecords(Sorted),

Seed=[
Groups={},
Output={}
],

Result=
List.Accumulate(
Rows,
Seed,
(state,person)=>

let

ExistingGroups=state[Groups],

Eligible=
List.PositionOf(
List.Transform(
ExistingGroups,
(g)=>
(
g[Count] <3 and
(if person[Wheelchair]=1 then g[Wheelchairs]<1 else true) and
(if person[Fall Risk]=1 then g[FallRisks]<1 else true)
)
),
true
),

UseGroup=
if Eligible=-1
then List.Count(ExistingGroups)+1
else Eligible+1,

NewGroups=
if Eligible=-1 then
ExistingGroups &
{
[
GroupNo=UseGroup,
Count=1,
Wheelchairs=person[Wheelchair],
FallRisks=person[Fall Risk]
]
}
else
List.Transform(
ExistingGroups,
each
if _[GroupNo]=UseGroup
then
[
GroupNo=_[GroupNo],
Count=_[Count]+1,
Wheelchairs=_[Wheelchairs]+person[Wheelchair],
FallRisks=_[FallRisks]+person[Fall Risk]
]
else _
),

NewOutput=
state[Output] &
{
[
Client Name=person[Client Name],
Assignment="Group "&Text.From(UseGroup)
]
}

in
[
Groups=NewGroups,
Output=NewOutput
]
),

Assignments=
Table.FromRecords(Result[Output]),

Merged=
Table.NestedJoin(
InputTable,
{"Client Name"},
Assignments,
{"Client Name"},
"Join",
JoinKind.LeftOuter
),

Expanded=
Table.ExpandTableColumn(
Merged,
"Join",
{"Assignment"},
{DayColumn&" Group"}
),

Final=
Table.AddColumn(
Expanded,
DayColumn&" Pregroup",
each
if Record.Field(_,DayColumn)=0
then "NOT PRESENT"
else if [Baseline]=1
then "RATIO 1:1"
else "RATIO 2:3"
)

in Final

I really appreciate your patience and assistance as I fumble my way through this process. 

[v-echaithra]

Hi @RBTuesday ,

At this stage, the logic itself looks correct, and the remaining issue appears to be related to the actual query structure or source data being passed into the function.

Would you be able to share a small sample `.pbix` file or anonymized sample Excel file reproducing the issue? That would make it much easier to identify the issue.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[RBTuesday]

I have put files into dropbox here Schedule Test Source XLSX  and here Schedule Test PBIX 

(all info is fake)

The data source table is on tab 1 on the excel file. The table on tab 2 is not currently being referenced, it was for some other group assignment experimentation I had tried before. 

I appreciate you!

[v-echaithra]

Hi @RBTuesday ,

Not able to access the file you shared. Could you please re-share it? Also, please provide the expected sample output for reference.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[RBTuesday]

Thank you. I have zero understanding of the coding in the Power Query side so I don't even remotely understand what you did there but it clearly works. Going to see if I can get it to work in my actual pbix file.

[grazitti_sapna]

Hi @RBTuesday,

I've created the sample .pbix file for you, Just copy the code from advance editor and paste it in your version, just replace the source in your version.

Hope this helps. Kindly like and mark as solution if it works for you.