cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CalfordMath
Advocate V
Advocate V

compare values to "this item's value" in expression

I'm using AddColumn within a ClearCollect.  Let's say my original data source has a column "score" with  numeric values.  The value for the new column I'd like to add will be the number of records that have a "score" less than or equal to the given score.  

In Excel, I would use something like:  =COUNTIF($A$1:$A$10,"<=" &A1) and copy that formula to the cells below so it would auto increment A1.. B1.. C1 etc.

 

In a Gallery, there is the handy "ThisItem" feature, so you can compare column values to a specific record's column value.  

 

I'm looking for something in PowerApps like this: 

ClearCollect(New_Collection, AddColumns(Original_Collection,"Lower_Score",CountIf(Original_Collection,score<=ThisItem.score))

only of course "ThisItem" isn't value outside a gallery.  How can I acheive something like this?

4 REPLIES 4
Mike8
Memorable Member
Memorable Member

Hello CalfordMath,

You can try this...

Button1.OnSelect : ClearCollect(ColScore,{Score:1},{Score:2},{Score:3},{Score:4},{Score:2},{Score:3})

Gallery1.Items : ColScore                                         --          (Inside the Gallery1 we have 2 labels.)
Label1.Text : ThisItem.Score
Label2.Text : CountIf(ColScore,Score<=ThisItem.Score)

So Label2 displays the number of records that have a "score" less than or equal to the given score that is being displayed on Label1.

Is this what you need?


Hi Mike8,

Thank you for taking the time to reply!  While your answer did not help my question about how to acheive "ThisItem" type references outside of a Gallery control, it did give me the idea to use invisible labels inside my gallery and simply call their .Text values like I would a variable in my expressions.  Originally, need was to create a new auto-incremented column in a Filtered collection.  I had a column in the original table that numbered 1 - 24, but if I filtered this down to only 5 records (not consecutive), I wanted a new column with values 1, 2, 3, 4, 5.  I was using the "countif less than my number" to determine where each record stood in the new list.  The problem was trying to acheive this in an AddColumns.  Rather than changing the shema of my collection before populating the Gallery, I just populate the Gallery and then set up an invisible label with Text expression set to give my desired numbering.  I had to Filter the original data source again within my CountIf, since it wouldn't accept Gallery.AllItems.  I won't mark this as the accepted solution, since it is really a workaround but doesn't solve the problem of accessing iteration values within expressions of functions like Collect.  

 

It would be very nice if Gallery Controls had variables that worked exactly like the invisible labels I created.. having a local value for each record .. is it a sin to use the word ARRAY in a powerapps forum lol.

Hello CalfordMath,

What about this example? Does it help?

You will need a texiInput , a button and a gallery.

Button1.OnSelect:
ClearCollect(Test,{ID:5,Text:"A"},{ID:6,Text:"B"},{ID:9,Text:"C"});
ClearCollect(ColNumbers,1,2,3)

Gallery1.Items:
ForAll(Filter(ColNumbers,Value<=CountRows(Filter(Test,ID>Value(TextInput1.Text)))),{ID:Last(FirstN(Filter(Test,ID>Value(TextInput1.Text)),Value)).ID,Text:Last(FirstN(Filter(Test,ID>Value(TextInput1.Text)),Value)).Text,NumberOfRows:Value})

The gallery will contain 3 labels. --->    ThisItem.NumberOfRows ThisItem.Text ThisItem.ID
Try the values 5 or 6 in the textinput and check the results in the gallery

Hey Mike, that code works to filter a Gallery to values greater than a specified, and it does give each row a consecutive numbering starting at 1.  It is limited by the need to explicitly create the ColNumber collection.  I have a collections of different sizes based on the selection from a drop down... it's very complicated to explain.  But thanks for you efforts.  I have working code now.. to get my new increment value for each record in the filtered collection dynamically, I make a label in my Gallery and set it's text property to this:

 

CountIf(Filter([originallist],[filter condition]),number<=ThisItem.number)

 

Doing this in a Gallery was never a problem.  I just wondered if it was possible to use the iteration record values in the expression OUTSIDE a gallery, when using AddColumn within a Collect.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,878)