cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hockeyman9474
Level: Powered On

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
mr-dang
Level 10

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
mr-dang
Level 10

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

Hockeyman9474
Level: Powered On

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?

mr-dang
Level 10

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
Hockeyman9474
Level: Powered On

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?

Hockeyman9474
Level: Powered On

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?

Hockeyman9474
Level: Powered On

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)

mr-dang
Level 10

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
Hockeyman9474
Level: Powered On

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.

Hockeyman9474
Level: Powered On

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,936)