The ISFIRSTOCCURRENCE function returns a Boolean value of TRUE for the first occurrence of a value in a list dimension.

For example, you can use ISFIRSTOCCURRENCE to prevent duplicates when creating lists from data.

ISFIRSTOCCURRENCE(Values to compare, List dimension to search)

ArgumentData typeDescription
Values to compareNumber, Boolean, date, time period, list, or textThe values to search for the first occurrences of.
List dimension to searchList

The list to search along for the first occurrences of the Value to find argument.

Must be a dimension of the target line item.

The ISFIRSTOCCURRENCE function returns a Boolean result.

In Polaris, you can use the ISFIRSTOCCURRENCE function with the Time list. In the Classic Engine, you cannot.

Additionally, in Polaris, the ISFIRSTOCCURRENCE function does not have a 50 million cell limit as it does in the Classic Engine.

In Polaris, the dimension argument must match one of the dimensions of the target line item. For example, you cannot use a subset of a list as a dimension argument. In the Classic Engine, this is not the case. 

Note: Performance of ISFIRSTOCCURRENCE is known to be poor in Polaris for models with high dimensionality. We strongly advise you to avoid ISFIRSTOCCURRENCE in Polaris.

ISFIRSTOCCURRENCE(Product, Contracts)

You can reference the Users list with the ISFIRSTOCCURRENCE function. However, you cannot reference specific users within the Users list as this is production data, which can change and make your formula invalid.

The ISFIRSTOCCURRENCE function references the order of the leaf list as seen in General Lists, not the parent list. This means that the first occurrence of an item may not be the first that displays in a list within a module. You can change the order of lists with the Order List action.

An artificial limit is imposed to prevent the searching of large data sets that would slow down the server. This limit is set at 50 million cells. If more than 50 million cells are used with the ISFIRSTOCCURRENCE function, the model rolls back and a notification displays.

The 50 million cell limit does not account for summarized values or the Time and Versions lists. This means you can use the ISFIRSTOCCURRENCE function with a line item with a Cell Count of greater than 50 million cells if there are less than 50 million nonsummarized cells.

As the number of cells you use with the ISFIRSTOCCURRENCE function increases, so does the duration of the calculation.

In this example, a Contracts list displays on rows, and line items on columns. The line items contain information about each contract, and the Is first occurrence? line item uses the ISFIRSTOCCURRENCE function to identify the first contract for each product.


ProductTransaction amount

Is first occurrence?

ISFIRSTOCCURRENCE(Product, Contracts)

Contract 1Peaches1,216.40
Contract 2Peaches864.20
Contract 3Peaches2.165.60
Contract 4Bananas3,485.00
Contract 5Bananas1.692.10
Contract 6Peaches1,451.20

The Values to compare argument can be any valid expression. In this example, a Transactions list displays on rows, and line items on columns.

Each transaction has a fee subtracted from it. The ISFIRSTOCCURRENCE function is used to identify the first occurrence of each value after subtraction.


Transaction valueTransaction fee

First occurrence of transaction value

ISFIRSTOCCURRENCE(('Transaction value' - 'Transaction fee'), Transactions) 

Transaction 11,00025
Transaction 21,100125 
Transaction 350010
Transaction 455060 
Transaction 51,05075