LookUp with Multiple Condition

[akbarmahfuzalam]

Hi All,

I am looking for help in Lookup Function in Powerapps.
I have two galleries and I want the Text which is HB Hours to be populated with sum of another Gallery which has multiple entries of the same User Name Queue Name and date.

I want total duration in Hb Hours

What formula shall set to get the desired result

Thanks in advance

[zaphod88]

Are there lists/collections for both of the galleries?
Try
Sum(Filter(Gallery2.AllItems, 'Process Date'.SelectedDate = DateGallery2.SelectedDate, 'User Name'.Text = UserNameGallery2.Text, 'Queue Name'.Text = QueueNameGallery2.Text).TotalDuration.Text)
You have to replace the input names with your own, of course.

[akbarmahfuzalam]

@zaphod88 

Thanks for your answer.

I tried to implement the code but unfortunately, it didn't work for me.

And I have 

Activity Name is Queue Name

ShiftDate is WorkedDate

EID is UserName/Title

[zaphod88]

Try:

Sum(Filter(GalleryHB.AllItems, Label2_4.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name').tDuration)

I hope I got all column and label names right - if not, you may have to correct them. You already get a delegation warning - I think it's a good idea to ClearCollect(CollHBData,HBData) and use the collection instead of the SP list.

[akbarmahfuzalam]

@zaphod88 

I tried the below formula and even i tried to used the CollHBData but its fetching an error

---

@zaphod88 wrote:

Try:

Sum(Filter(GalleryHB.AllItems, Label2_4.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name').tDuration)

I hope I got all column and label names right - if not, you may have to correct them. You already get a delegation warning - I think it's a good idea to ClearCollect(CollHBData,HBData) and use the collection instead of the SP list.

---

Sum(Filter(GalleryHB.AllItems, Lbl_WorkedDate.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name').PDur)

[zaphod88]

My fault. Try replacing the point before PDur with a comma, so:

Sum(Filter(GalleryHB.AllItems, Lbl_WorkedDate.SelectedDate = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name'),PDur)

[akbarmahfuzalam]

@zaphod88 

Still, I find an error as mention below.

I tried the below formula as well

Sum(Filter(CollHBData, Lbl_WorkedDate.Text = Text(ShiftDate,DateTimeFormat.ShortDate,"en-US"), Lbl_UserName.Text = EID, Lbl_QueueName.Text = 'Activity Name'),PDur)

But I am not getting any value. The label is blank.

[zaphod88]

Maybe a silly question, but: Are there any items that match the Filter() conditions? Enter a name, a date and a Queue Name that are corresponding to an item in the gallery/the Sharepoint list into the labels and see if it works then. Maybe there is no item in the Sharepoint list that matches all three criteria.

If PowerApps is showing no errors, at least the syntax is correct. As I don't know if I got all those labels right, try to remove 1 or 2 conditions for testing purposes and see if it works then. You could also try to replace the Text(...) just by ShiftDate.

[akbarmahfuzalam]

@zaphod88 

I tried that way as well

[zaphod88]

Found a problem:

Could you try it again after correcting?