Help building a random assignment logic to my contracts table

[juliamacg_]

Hello. I have 2 tables on Power BI: FAT_REGISTROS and DIM_STATUS.

FAT_REGISTROS stores all of the contracts registered by my company along with their current pending status. Here's a sample:

DIM_STATUS contains the analysts that should work on each contract according to their status. Some names are repeated because these analysts are meant to be assigned to more contracts than others. Here's a sample:

This assign-worker-to-contract-according-to-their-status job is done manually by the team every day. I'd like to use Power BI to do that automatically in a random order, as long as their assignment frequency is followed (for example, 50% of all contracts in status "Pending approval" are meant to be assigned to Ashley, while the remaining 50% must be distributed equally between Natalie and Hannah).

Is that possible? If so, how could I do that? I've tried a number of different ways and no one worked. Not even ChatGPT worked this one out for me.

Important info to share:

1. FAT_REGISTROS has no column without duplicates. I tried to relate both by the "Status" field in a many-to-many relation.

2. Not all statuses a contract can be in on FAT_REGISTROS are on DIM_STATUS, and that's expected.

3. The logic of repeating names on DIM_STATUS was done manually by me as I thought it would be a good idea to get the software to understand the assignment frequency, but if it's not I can change it to something else without much trouble.

Any ideas? Thanks!

[littlemojopuppy · Accepted solution]

@juliamacg_ this would be very ugly and involve some pretty heavy DAX to accomplish.

You'd also have to randomly assign contracts to this person instead of that based on status.  And based on a given allocation from step one, you'd have to define rules for each person as allowed to have up to a certain threshold of contracts of a certain status.  And then you'd have to have a third set of rules to make sure that any given person wasn't overloaded on any given status.  And finally, I would assume a fourth set to make sure the allocation was optimized.

It could probably be done, but the DAX involved is going to be really, really intensive to accomplish.  If you have access to other platforms (Python, R, SQL) I'd suggest doing the heavy lifting there and pulling the results into Power BI...or skip Power BI all together.

Just my two cents...