How to find the first non-null value in a column
A common Sigma formula use case is to return the first non-null value in a column. This document clarifies a common misconception about how to accomplish this with Coalesce, and walks through a recommended implementation using ListAgg and SplitPart.
Sample dataset
This document uses a sample dataset of 911 calls, grouped by the Incident ID.
Why you should not use Coalesce
For this scenario, for each Incident ID in the 911 calls, you want to know which was the most recently dispatched agency. One common misstep is using Coalesce, which does return the first non-null value from the arguments provided, but does not have the ability to traverse a column's values. If you add a column beside [Incident ID] using Coalesce, the formula returns an asterisk indicating that multiple values have been identified:
A more effective solution using ListAgg and SplitPart
First, use the ListAgg function to return a list of the non-null values in the grouping:
Next, extract the first value using SplitPart. SplitPart takes in a string as its first argument, followed by a separator (a character sequence on which to split the string into separate pieces), and an index (indicating which piece of the string to return). The formula looks like:
SplitPart(ListAgg([Dispatched Agency]), ",", 1)
The formula passes the [Dispatched Agency] column into ListAgg, returning a comma separated list of all non-null values. Then, it passes that list into SplitPart, which splits the string into pieces on each comma that appears in the list, and returns the first piece from that list. The end result will look like this:
If your dataset already contains comma separated values, you can pass in another optional argument to ListAgg to separate that list by a different character sequence:
In the screenshot above, notice that the [Responding Agency] values now contain commas. This means that the original ListAgg function splits 'Police Department, Cental Precinct' into two values, which would cause SplitPart to only return 'Police Department'. You don't want that, so instead you adjust the formula to pass in ">>" as a separator for the ListAgg output and pass in ">>" as our separator argument to SplitPart. The resulting formula looks like this:
SplitPart(ListAgg([Dispatched Agency], ">>"), ">>", 1)