cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Distinct Filter

I ma new to Powerapps and attempting to learn the basics here. I am developing my first app and my datasource is an excel spreadsheet. I have 3 screens in my app, the first is a splash screen with an enter button, the second is a list box that displays the contents of the first column called Location. The locations that I am using are 6 numerical characters long i.e. 020128. There are other records that are not. I created a formula that filters out the ones I don't need, however what's left there can be more than 1 of. therefore I need a distinct list. The code below works to filter, but how do I make it distinct as well?

 

Filter(Table1,Mid(Location,4,1)<>" ",Mid(Location,4,1)<>"-")

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Distinct Filter

Hi,

Filter(Table1,Mid(Location,4,1)<>" ",Mid(Location,4,1)<>"-")

Your formula currently means, "Filter Table1 so that it only shows records where there is not a space or hyphen in the 4th character."

 

You can wrap Distinct around it and cite Location as the column for which you need distinct values. Table1 will be reduced to one column called "Result."

 

Distinct(Filter(Table1,Mid(Location,4,1)<>" ",Mid(Location,4,1)<>"-"),Location)

This means, "Give the distinct values of location that fit the condition in the filter."

Microsoft Employee
@8bitclassroom

View solution in original post

10 REPLIES 10
Highlighted
Community Champion
Community Champion

Re: Distinct Filter

Hi,

Filter(Table1,Mid(Location,4,1)<>" ",Mid(Location,4,1)<>"-")

Your formula currently means, "Filter Table1 so that it only shows records where there is not a space or hyphen in the 4th character."

 

You can wrap Distinct around it and cite Location as the column for which you need distinct values. Table1 will be reduced to one column called "Result."

 

Distinct(Filter(Table1,Mid(Location,4,1)<>" ",Mid(Location,4,1)<>"-"),Location)

This means, "Give the distinct values of location that fit the condition in the filter."

Microsoft Employee
@8bitclassroom

View solution in original post

Highlighted
Helper III
Helper III

Re: Distinct Filter

Perfect. Thank You. One more quick question realted to this. All results I am supposed to see are not shown. Is there a setting that limits results to a specific amount of records?

Highlighted
Community Champion
Community Champion

Re: Distinct Filter

Distinct(Filter(Table1,Mid(Location,4,1)<>" ",Mid(Location,4,1)<>"-"),Location)

In the formula above, the Not, or <>, function is not delegated, meaning that your Filter will only operate on the first 500 records. Distinct is also not delegated and only operates on the first 500 records. 

 

So the formula works in theory, but not necessarily in practice 🙂 There's been many threads about the 500 limit, and I hope it is resolved soon.

Microsoft Employee
@8bitclassroom
Helper III
Helper III

Re: Distinct Filter

That is some limitation! Is there a way to accomplish the task at hand with a different formula or possibly even summing?

Highlighted
Helper III
Helper III

Re: Distinct Filter

That is some limitation! I have some questions regarding the "limitation".

What exactly do you mean by delagated?

Is there a limitation if the formula doesn't delegate?

Does the limitation occur at the point of reading the file or at the point where information is going to be displayed?

 

What I mean from the last question is this:

My datasource obviously exceeds 500 records. There are many lines that aren't needed, but I don't want to edit them out at oin the datasource everytime I update it. I rather do it through exclusions in the app. Is there another way to accomplish this by changing the formula so that it doesn't use delegation.

 

Here is a snippet of my datasource:

Location            Carline       Daily     10Day     MTD     YTD     DlrInv     D/S

020-00-NONE   Sedan        10          20          100       200      40          20    

020 SDC           SUV

020-01 SDC      Full

020128             Compact

 

First, I am not going to fill in all the numbers, assume there is Data for all lines.

Second, I am only interested in locations formatetd like the last one.

Third, b/c this is a snippet, imagine there being 1 record for each location, for each carline. If there are 65 locations and 7 carlines, that is 455 records plus all the records I don't want to display puts me over the 500 mark which I suppose answers a previous question that the truncating occurs at the point of reading the datasource?

Highlighted
Helper III
Helper III

Re: Distinct Filter

As an update I figured out how to use the Length function with Distinct. So I am querying the same information as before, but still can't pull more than 500 lines.

 

Distinct(Filter(Table1,Len(Location)=6),Location)

Highlighted
Community Champion
Community Champion

Re: Distinct Filter

If you can Filter the formula with a condition that is simple enough, Collect it to a local collection.

 

If you apply Distinct on the local collection, it will work on the whole thing.

Microsoft Employee
@8bitclassroom
Highlighted
Helper III
Helper III

Re: Distinct Filter

https://powerapps.microsoft.com/en-us/tutorials/create-update-collection

 

 

I'm reading up on collections here and it walks you through a text box for input and a button that adds the text to a collection. What I don't see is how to create a collection connected to the datasource.

Highlighted
Helper III
Helper III

Re: Distinct Filter

https://powerapps.microsoft.com/en-us/tutorials/create-update-collection

 

 

I'm reading up on collections here and it walks you through a text box for input and a button that adds the text to a collection. What I don't see is how to create a collection connected to the datasource.

 

I tried using the on select for my list box with this formula:

 

Collect(Table1,Location

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (11,077)