Power Pivot Principles: The A to Z of DAX Functions – GENERATESERIES

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at GENERATESERIES.

The GENERATESERIES function

The GENERATESERIES function is a simple and useful function, but it is not yet compatible with Power Pivot.  Nonetheless, it’s still worthwhile to have a look at its functionality.  For this article, we will use Power BI to demonstrate the function.

The GENERATESERIES function generates a single column table containing the values of an arithmetic series (i.e. a sequence of values which differs from the preceding entry by a constant quantity).  It has the following syntax:    

• startValue: this is required and it is the initial value of the series
• endValue: this is required and it is the end value of the series
• incrementValue: this is optional and it is the increment value of the sequence; it must be a positive value; when not provided, the default value is one [1].

It should be noted that:

• when endValue is less than startValue, an empty table is returned
• incrementValue must be a positive value (see above)
• the sequence stops at the last value that is less than or equal to endValue
• the GENERATESERIES function is not compatible with Power Pivot and currently it is only compatible with Power BI, SSAS Tabular, Azure AS and SSDT
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Let’s look at a few examples.

The first example has a startValue of one [1] and an endValue of ten [10], with the incrementValue default to be one [1].  We thus obtain a single column with the series from one [1] to ten [10].

Let’s consider a second example:

This formula creates a series from one [1] to four [4] with each step being 0.5:

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.