cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter a table to get max in a column, for each unique value in another column of the same table

Hi guys,

 

i am struggling with a solution to the below issue,

 

Price Table: [dbo.Vw_Prices]

Customer_ID   | Site_ID   | Price_ID(PK)    | Effective_Date

5                      | 1            | 100                 | 1-1-2001

5                      | 2            | 123                 | 1-5-2001

5                      | 1            | 145                 | 1-2-2001

2                      | 3            | 124                 | 5-3-2001

2                      | 4            | 165                 | 4-3-2001

3                      | 5            | 196                 | 21-1-2001

1                      | 6            | 156                 | 4-5-2001

1                      | 6            | 197                 | 9-1-2001

3                      | 5            | 186                 | 1-3-2001

 

What i want to get is the most recent effective price (Effective_Date), for all the sites (Site_ID) registered to the a customer (Customer_ID). Customer and effective date will be picked based on User input. My table has data more than 2,000 rows and is frequently updated.

This is the Code im trying to use but it give all the prices for all the sites for the customer

 

 

SortByColumns(
Filter(
'[dbo].[Vw_Prices]',
Customer_ID = Var_SelectedCust.Customer_ID,
Effective_Date <= Inp_PricingDate.SelectedDate
),
"Site_ID",
Descending,
"Effective_Date",
Descending
)

 


I tried working with Filter and Group by but cant fully get that to work. I have a Azure SQL, set for the data source

 

Thanks in advance for any help.

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Filter a table to get max in a column, for each unique value in another column of the same table

 

 

 

GroupBy(
    SortbyColumns(
        '[dbo].[Vw_Prices]',
        "Effective_Date",
        Descending,
         "Site ID",
        Descending
    ),
    "Site_ID",
    "Restofdata"
)

 

In the outer gallery Items property and First(Restofdata) in the inner gallery, Nested galleries on left.  Same nested gallery w/o First() on inner gallery.

Annotation 2020-05-23 091753.jpg

View solution in original post

4 REPLIES 4
Highlighted
Super User III
Super User III

Re: Filter a table to get max in a column, for each unique value in another column of the same table

 

 

 

GroupBy(
    SortbyColumns(
        '[dbo].[Vw_Prices]',
        "Effective_Date",
        Descending,
         "Site ID",
        Descending
    ),
    "Site_ID",
    "Restofdata"
)

 

In the outer gallery Items property and First(Restofdata) in the inner gallery, Nested galleries on left.  Same nested gallery w/o First() on inner gallery.

Annotation 2020-05-23 091753.jpg

View solution in original post

Highlighted
Frequent Visitor

Re: Filter a table to get max in a column, for each unique value in another column of the same table

Thanks for the suggestion, however this is evaluating only the first 500 records and not any after, due to delegation limit of "Groupby" function. any way to replace group by?

Highlighted
Super User III
Super User III

Re: Filter a table to get max in a column, for each unique value in another column of the same table

@Rifmont 

You can increase your limit to 2k. File>Settings>Advanced settings>Data row limit  change from 500 to 2k.   Otherwise you will have to either filter your dataset down to less than 2k and then apply the GroupBy() or create a collection from the entire dataset. There are a number of posts on how to create a collection from large sharepoint lists. 

Highlighted
Frequent Visitor

Re: Filter a table to get max in a column, for each unique value in another column of the same table

Thank you. Nested galleries seems to work best so far. I had to make sure the filter function used returned records less than 2000. Then apply groupby on that.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,873)