Blog

Formula - How to display a count of grouped non-blank and blank rows

If you need to produce a count of non-blank rows, this post walks through an example scenario and highlights the use of the little-known function, CountA.

There are occasions where it's necessary to produce a count of non-blank records. Fortunately, PowerFX provides a functions that can carry out this task, including the CountA function.

The official documentation for the count-related functions is here.

It can often be difficult to understand how to apply these functions by reading the documentation, the purpose of this post is to provide a bit more explanation by providing an example with some additional context. This post also covers the syntax to count blank rows.

Example data structure

To demonstrate, this post takes the parent-child relationship that's illustrated below. With this example, there's a parent table that stores property types, and a table of properties. A 'PropertyTypeID' value links these two tables. 



In this post, we'll find out how to produce a count of records where the TaxBand value is either blank, or not blank.

Example App setup

To demonstrate, we'll build a screen that contains a set of nested galleries. This will help describe how to carry out a count of records with empty or non-empty tax bands, based on the property type id.

The example screen is shown beneath.


With this example, the Items property of the parent gallery (galParent) is set to this:

PropertyTypes
The items property of the nested child gallery (galChild) is set to this:

Filter(Properties, ThisItem.PropertyType_ID=PropertyTypeID)

How to count non-blank rows based on a group

To display a count of records where the tax band is not blank, we can add a label to the item template of the parent gallery and set the text property of the label to the following formula

CountA(galChild.AllItems.TaxBand)



The CountA function takes a single-column table. In this example, we specify the tax band column with the identifier galChild.AllItems.TaxBand. 

Suppose we want to count non-blank rows for the entire 'Properties' data source. To do this, we would use the following syntax:

CountA(Properties.TaxBand)

 

As I'll mention later in this post, here's an alternative formula that's delegable with a Dataverse data source.

CountIf(Properties.TaxBand, !IsBlank(TaxBand))

How to count blank rows based on a group

Since there's no inverse of the CountA function, we can count blank rows by calling the CountIf function and specifying a condition that defines blank rows only.

CountIf(galChild.AllItems, IsBlank(TaxBand))


Delegation Issues

It's important to mention the problem of delegation. In this example, this is not a great issue because we're counting from grouped items in a gallery, but if we were counting rows against a large data source, it's worth noting that all count-related functions in PowerFx are not delegable and will not return accurate results. This is particularly an issue with SharePoint.

One important thing to mention is Dataverse, where we can enable delegation for the CountRows and CountIf functions by turning on the 'Enhanced delegation for Microsoft Dataverse' setting in the app settings.

Delegation support for the UpdateIf function will come soon in 2023, so these are great reasons why we should consider using Dataverse as a data source.


Conclusion

This post provided an example of how to display a count of non-blank and blank groups, grouped by a data value. In particular, it highlighted the use of the CountA function and provided an example of how to call this function.
Previous
Next
Related posts