Display unique set of items from a sharepoint list

[bagavathv1990]

I have a sharepoint list of Hospitals with City and Country but different studies. So there will be multiple rows of same hospital with different rows. 

I need to have a gallery to just show Hospital name , City and Country with filter options for all three fields but only distinct list.

It would be great if someone would help on this. 

I tried distinct values but i could show only the hospital name, i also need to show the corresponding city and country 

Thanks in advance

[WarrenBelz]

Hi @bagavathv1990 ,

Assuming the hierarchy is Country/City/Hospital - Dropdown1 (Country)

Sort(
   Distinct(
      SPList,
      Country
   ),
   Value
)

Dropdown2 (City)

Sort(
   Distinct(
      Filter(
         SPList,
         Country = Dropdown1.Selected.Value
      ),
      City
   ),
   Value
)

Dropdown3 - Hospital

Sort(
   Distinct(
      Filter(
         SPList,
         Country = Dropdown1.Selected.Value &&
         City = Dropdown2.Selected.Value
      ),
      Hospital
   ),
   Value
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[bagavathv1990]

Below is the query for my gallery Items which returns Hospital, City and Country based on the filter but duplicates. Sorry i am a beginner at this, can you guide me where i can use the above queries

Search(
Filter(
'Hospital List',Country.Value in ComboBox1_COUNTRY_1.SelectedItems.Value || IsBlank(ComboBox1_COUNTRY_1.Selected.Value) || IsEmpty(ComboBox1_COUNTRY_1.SelectedItems),
'Product Name'.Value in ComboBox_City_1.SelectedItems.Value || IsBlank(ComboBox_City_1.Selected.Value) || IsEmpty(ComboBox_City_1.SelectedItems)),TextInput1_3.Text,"Hospital Name")

[WarrenBelz]

@bagavathv1990 ,

You only have Country (matching Country field) and 'Product Name' matching your City drop-down (???), so I am not sure what you are asking here. You need to add Hospital with the same structure and confirm the actual field names and field types that should match the drop-downs as well as the other Combo Box name.

[bagavathv1990]

I have Hospital Name and City as Text field, Country and Product as Choice Field. I am trying to filter the Gallery as shown below. 

Sorry about the query, Product name was supposed to match the study combo field, I am not trying to filter by City. Though i am trying to filter by Product name, i want to show only the Hospital Name, City and Country in the Gallery

[WarrenBelz]

@bagavathv1990 ,

Assuming both those Combo Boxes are multiple choice, the only duplicates you should get are hospitals starting with the same characters that are in your Search box. Either that or you have duplicate hospital names in your list.

[bagavathv1990]

You are right, if i select a product value in the combo box, i get only distinct list. but if i dont want to filter by Product, i get the duplicates which i understand is not really a duplicate based on different products, since in my gallery i want to show only the Hospital Name, City and Country, i see same rows repeating. For eg:

Hospital Name Text	City Text	Country Choice	Product Choice
Hospital 1	City 1	Country 1	Product A
Hospital 1	City 1	Country 1	Product B
Hospital 1	City 1	Country 1	Product C
Hospital 2	City 2	Country 2	Product A
Hospital 3	City 3	Country 3	Product D
Hospital 4	City 4	Country 4	Product A
Hospital 4	City 4	Country 4	Product D

is my list. In m gallery i want to show only hospital name  City and country. So i only want to see

Hospital 1	City 1	Country 1
Hospital 2	City 2	Country 2
Hospital 3	City 3	Country 3
Hospital 4	City 4	Country 4

[WarrenBelz · Accepted solution]

@bagavathv1990 ,

This should do it

GroupBy(
   Search(
      Filter(
         'Hospital List',
         Country.Value in ComboBox1_COUNTRY_1.SelectedItems ||
         Len(ComboBox1_COUNTRY_1.Selected.Value) = 0,
         'City Name' in ComboBox_City_1.SelectedItems ||   
         Len(ComboBox_City_1.Selected.Value) = 0
      ),
      TextInput1_3.Text,
      "Hospital Name"
   ),
   "Country",
   "City Name",
   "Hospital Name",
   "Data"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[bagavathv1990]

This worked, I just had to add a column for the Country field since it is a choice column before Grouping those columns