Automatically assign resources to New tickets

[Newbie22]

Hi Everyone,

I'm currently building a tracker on power bi and I wish to have a feature on which powerbi can automatically assign/designate resources to attend new tickets. Let's say for example:

I have below resources (list is in excel and connected to existing PBI). This will be updated everyday whoever are available that day. I would like them to assign to new tickets based on their % of distribution. Michael John has 50% because he's the senior. Leslie has the least % because she's a new hire. Is there any work around here?

User ID	Resource Name	% of Distribution
MK12345	Michael John	50%
MK12346	Chris Kyle	30%
MK12347	Leslie Jane	20%

Here's a sample raw data if you want to try. All are new tickets.  Let me know if you need more information. Thank you in advance 

Case Number	Assigned To
HRC13210422
HRC13234408
HRC13234460
HRC13234836
HRC13235056
HRC13235106
HRC13235366
HRC13235386
HRC13235413
HRC13235497
HRC13235532
HRC13235611
HRC13235669
HRC13235706
HRC13235723
HRC13235776
HRC13236078
HRC13236121
HRC13236210
HRC13236229
HRC13236244
HRC13236431
HRC13236896
HRC13237019

[Anonymous · Accepted solution]

Hi @Newbie22 ,

Create below columns on user table:

cumulative_% = SUMX(FILTER(ALLSELECTED(User),User[User ID]<=EARLIER(User[User ID])),User[% of Distribution])+0

end = ROUNDUP(User[cumulative_%]*MAX('Case'[Index]),0)

start = MAXX(FILTER(ALLSELECTED(User),User[User ID]<EARLIER(User[User ID])),User[end])+0

Then create below column to case table.

assign to = CALCULATE(MAX(User[Resource Name]),FILTER(User,User[start]<'Case'[Index]&&User[end]>='Case'[Index]))

[Anonymous]

Hi @Newbie22 ,

So you want to add Resource Name to Assigned To column?

Best Regards,

Jay

[Newbie22]

That's right @Anonymous 🙂

Assigned To will be based on the 3 resource names 😉

[Newbie22]

Up 😊