cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Sum( If( IsBlank assistance

Hello, 

 

I have a series of labels that are fed from a LookUp function. I want to have a count at the end of the form which shows users the #/Total labels that are filled. 

 

If label has no text, it receives a "0". If it has text, it receives a "1". I want to sum the "1" 's at the end of the row like so: 

 

NOTE: The count below should read "2/13" because 2 labels have text.

 

gallery2.png

 

Here is the function I have so far, but its not giving me a sum of all the "1"'s.  

Sum(
    If(
        IsBlank(
            label_complianceComp1 || 
            label_complianceComp2 ||
            label_complianceComp3 ||
            label_complianceComp4 ||
            label_complianceComp5 ||
            label_complianceComp6 ||
            label_complianceComp7 ||
            label_complianceComp8 ||
            label_complianceComp9 ||
            label_complianceComp10 ||
            label_complianceComp11 ||
            label_complianceComp12 ||
            label_complianceComp13
        ),
    0,1)
) 
& "/13"

Can someone give me a pointer on what this function is missing? 


Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft Employee

Re: Sum( If( IsBlank assistance

 

Hello ericonline,

 

Interesting problem!

 

The Sum() function takes 1 or more arguments and sums them up. What's happening here is the || (Or) function is returning false, because none of the controls are true (they can't be, they're controls. This is different from programming in JavaScript, for example, which evaluates whether operands are truthy or falsey. PowerApps just evaluates whether it's actually true here). So, we get the following: 

 

Sum( If( IsBlank(false), 0, 1) )

Sum( If( false, 0, 1) )

Sum( 1 )

1

 

So that's why you're experiencing what you are. But how do we fix it? I feel like there should be a nice elegant solution, but I haven't been able to come up with it, yet. I do know a solution, which I'll share, but if someone figures out a better one, please feel free to add it to the discussion!

 

Anway, I think the following will work for you:

 

 

Sum(
    ForAll(
        [
            Value(label_complianceComp1.Text),
            Value(label_complianceComp2.Text),
            Value(label_complianceComp3.Text),
            Value(label_complianceComp4.Text),
            Value(label_complianceComp5.Text),
            Value(label_complianceComp6.Text),
            Value(label_complianceComp7.Text),
            Value(label_complianceComp8.Text),
            Value(label_complianceComp9.Text),
            Value(label_complianceComp10.Text),
            Value(label_complianceComp11.Text),
            Value(label_complianceComp12.Text),
            Value(label_complianceComp13.Text)
        ], 
        If( !IsBlank(Value), 1, 0 )
    ), 
    Value
)
& "/13"

 

 

What this does is it gets the Text property of each label and finds the value of that using the Value() function. It makes a table of these values (basically a list). Then, it uses the ForAll() function to loop over these values and return a table of 1's and 0's (1 if not blank, 0 if blank). Then, it uses the version of the Sum() function that works on tables ( Sum(table, expression) ) to sum all of these values up. Then it joins it to "/13" like you were doing before.

 

 

So, that will work. One thing you might want to consider, though, is making a horizontal gallery instead of all of these labels. If you can give the horizontal gallery a table of the values as text, you can just have one label inside the gallery with Text=ThisItem.Value and the sum expression can simplify to (assuming it's still strings of non-zero numbers or blank):

 

Sum(
    ForAll(
        MyGallery.AllItems,
        If( !IsBlank(Value), 1, 0 )
    ), 
    Value
)
& "/" & CountRows(MyGallery.AllItems)

 

 

Finally, here's some relevant documentation links, if you want to learn more:

ForAll Function

Sum Function (and other similar aggregate functions)

Value Function

CountRows Function (and other similar functions)

Gallery Control

 

Anyway, I hope this helps!

- Paul C.
Software Engineer on PowerApps

View solution in original post

2 REPLIES 2
Microsoft Employee

Re: Sum( If( IsBlank assistance

 

Hello ericonline,

 

Interesting problem!

 

The Sum() function takes 1 or more arguments and sums them up. What's happening here is the || (Or) function is returning false, because none of the controls are true (they can't be, they're controls. This is different from programming in JavaScript, for example, which evaluates whether operands are truthy or falsey. PowerApps just evaluates whether it's actually true here). So, we get the following: 

 

Sum( If( IsBlank(false), 0, 1) )

Sum( If( false, 0, 1) )

Sum( 1 )

1

 

So that's why you're experiencing what you are. But how do we fix it? I feel like there should be a nice elegant solution, but I haven't been able to come up with it, yet. I do know a solution, which I'll share, but if someone figures out a better one, please feel free to add it to the discussion!

 

Anway, I think the following will work for you:

 

 

Sum(
    ForAll(
        [
            Value(label_complianceComp1.Text),
            Value(label_complianceComp2.Text),
            Value(label_complianceComp3.Text),
            Value(label_complianceComp4.Text),
            Value(label_complianceComp5.Text),
            Value(label_complianceComp6.Text),
            Value(label_complianceComp7.Text),
            Value(label_complianceComp8.Text),
            Value(label_complianceComp9.Text),
            Value(label_complianceComp10.Text),
            Value(label_complianceComp11.Text),
            Value(label_complianceComp12.Text),
            Value(label_complianceComp13.Text)
        ], 
        If( !IsBlank(Value), 1, 0 )
    ), 
    Value
)
& "/13"

 

 

What this does is it gets the Text property of each label and finds the value of that using the Value() function. It makes a table of these values (basically a list). Then, it uses the ForAll() function to loop over these values and return a table of 1's and 0's (1 if not blank, 0 if blank). Then, it uses the version of the Sum() function that works on tables ( Sum(table, expression) ) to sum all of these values up. Then it joins it to "/13" like you were doing before.

 

 

So, that will work. One thing you might want to consider, though, is making a horizontal gallery instead of all of these labels. If you can give the horizontal gallery a table of the values as text, you can just have one label inside the gallery with Text=ThisItem.Value and the sum expression can simplify to (assuming it's still strings of non-zero numbers or blank):

 

Sum(
    ForAll(
        MyGallery.AllItems,
        If( !IsBlank(Value), 1, 0 )
    ), 
    Value
)
& "/" & CountRows(MyGallery.AllItems)

 

 

Finally, here's some relevant documentation links, if you want to learn more:

ForAll Function

Sum Function (and other similar aggregate functions)

Value Function

CountRows Function (and other similar functions)

Gallery Control

 

Anyway, I hope this helps!

- Paul C.
Software Engineer on PowerApps

View solution in original post

Super User
Super User

Re: Sum( If( IsBlank assistance

Hello Paul, 


Thank you for the detailed reply. I really appreciate it and yes, your solution to sum all labels worked great.

 

Since you are familiar with this puzzle piece, I'd like to go out on a limb and push the conversation a bit further as I'm really stuck on the label query itself:

 

The labels are already part of a vertical Gallery as shown here. I'm nervous about nesting Galleries as I don't fully understand them.

gallery1.png

 

Each label is looking up records in a Sharepoint list like so. The "." notation on the end is very strange to me!

gallery2.png

 

Here is the Sharepoint list: 

data_source.png

 

The label query is not correct nor complete. 

  • Not correct because as you can see in the Sharepoint List, rdm_cost_center S0801 completed comp1_num on 6/1/2018 but its not showing in the label.
    • I thought LookUp would go down the column and pull the first record it found. This is not the case here. 
  • Not complete because the query is not comparing the current year, half, or month against the records to populate the label. 
    • I have global variables set for current year, current half, and current month in the app, but how do I compare them against the same values in each record, then return the results? 

There is a writeup on the issue here as well with a bit different context. Perhaps thats a better place to take this (if you'll come along 🙂 ). 

 

Its very cool how PowerApps opens up development to a population of folks who would otherwise not be engaged. Thank you for the very useful product you've developed. I'll be honest and say its right up there with OneNote in my top 2 products that Microsoft makes. 

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,894)