web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Formula - Filter -> So...
Power Apps
Unanswered

Formula - Filter -> Sort -> Left -> Distinct function

(0) ShareShare
ReportReport
Posted on by Community Power Pla... Microsoft Employee

Just wondering if anyone can help me with the above formual?

 

I want to filter active levels, then sort levels numerically, pick the first 2 numbers of the level and then group them up so the user and select only the levels.

And no I can't include another row into the table for now as I know this would be the easiest way.

Categories:
Building Power Apps
  • tchin-nin Profile Picture
    tchin-nin 779 on at

    Hi,

    Can you provide some more information about your data and what your trying to achieve ?

    Having your data structure as it is and what output data your are looking for would help us to guide you.

     

    Theo

     

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    SQL Data Table = "Locations"

    Table Columns = "LOCATIONNAME" & "STATUS"

     

    I'm trying to create a dropdown menu for a user to select a level.

    Then based on the level they pick, they are provided a list of locations on that level.

     

    DropDown1 = Filters "Status", Sorts "Location Name", Left "Location Name" & Distinct "Location Name" = 12-345XC -> "12"

    DropDown2 = LookUp "DropDown1" with StartsWith "DropDown1" = "12-345XC", "12-456XC", "12-567", etc

     

    E.g.

    DropDown1 = shows "60", "61", "65" -> select "60"

    DropDown2 = shows "60-300XC" & "60-500XC" only

     

    Can this even be done? I know it would be easier to just simply add "Level" into the database, but I don't have that access.

     

    LOCATIONNAMESTATUS
    60-300XCActive
    65-850XCNot Active
    60-500XCActive
    61-400XCNot Active
    61-540XCActive
    61-800XCActive
    65-100XCActive
    61-100XCActive
    65-700XCActive
    60-100XCNot Active
    65-800XCActive
  • tchin-nin Profile Picture
    tchin-nin 779 on at

    You may not had colimn into the DB but you have the AddColumns() function. This is not a delegable function so before thinking about a solution I would like to know how much rows contains your table ?

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    There's currently 9476 rows with a large amount of them being inactive.

    There's also 12 columns, most of which are not populated.

  • tchin-nin Profile Picture
    tchin-nin 779 on at

    Let say that less than 2000 rows are "actives" so you can use the AddColumns() function. Be careful if you have more than 2000 rows actives your drop-down might be incomplete. And you still have to increase the delegation limit in your app experimental features. 

     

    You can do :

    DropdownList1.Items:

    Distinct(AddColumns(Filter(Locations, Status="Active"),"Level",Left(LOCATION AME,2)),"Level").Level

     

    DropdownList2.Items :

    Filter(Locations,StartWith(LOCATIONNAME,DropdownList1.Selected.Value))

     

    Again I'm warning you about delegation issues using the the AddColumns() function .

     

    Theo

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    DropdownList1.Items:

    Distinct(AddColumns(Filter(Locations, Status="Active"),"Level",Left(LOCATION AME,2)),"Level").Level

    Doesn't work, but has the above highlighted.

    I delete this and it show's Level in the drop down. 

     

    DropdownList2.Items :

    Filter(Locations,StartWith(LOCATIONNAME,DropdownList1.Selected.Value))

    Entire formula is red.

     

    I had similar if not the same formula's to the above but they haven't been working for me.

  • tchin-nin Profile Picture
    tchin-nin 779 on at
    So in the dropdownlist1 you have what you wanted ? Distinct values of the 2 first digits of the Location ?

    What is the error message for the second drop-down ?
  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    No, the the first dropdownlist1 shows only what is in the highlighted green section below as 1 option. 

     

    Distinct(AddColumns(Filter(Locations, Status="Active"),"Level",Left(LOCATION AME,2)),"Level").Level

    Hence Dropdownlist1 = "Level" only and not the new column list of Level.

     

    The error on the second dropdownlist is "The function filter has some invalid arguments."

     

  • tchin-nin Profile Picture
    tchin-nin 779 on at

    My bad, try it like this, without double quotes :

    Distinct(AddColumns(Filter(Locations, Status="Active"),"Level",Left(LOCATIONNAME,2)),Level)

     

    And for the second dropdown, the correct spelling is StartsWith() //and no StartWith as I wrote. Sorry I'm doing this on my phone, no PowerApps autocomplete yet !

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Ok so it works now.

     

    How can I sort the list and then add another item in the list to include in the filter? I.e. "Not Active" and "Active"

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 383 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 356

#3
WarrenBelz Profile Picture

WarrenBelz 232 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans