Forum Discussion

Newbie22's avatar
Newbie22
Icon for Resolver I rankResolver I
3 years ago
Solved

Automatically assign resources to New tickets

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 IDResource Name% of Distribution
MK12345Michael John50%
MK12346Chris Kyle30%
MK12347Leslie Jane20%

 

 

 

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 NumberAssigned 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 
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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]))

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Newbie22 ,

     

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

     

    Best Regards,

    Jay

    • Newbie22's avatar
      Newbie22
      Icon for Resolver I rankResolver I
      3 years ago

      That's right Anonymous ğŸ™‚

       

      Assigned To will be based on the 3 resource names ğŸ˜‰