If you ever need to create a list of numbers (either decimal or whole number), or list of dates and times, there is a very simple and useful function in DAX which helps. GenerateSeries is a simple function to use to create a list. In this article, I’ll explain how you can use this function. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.
There are a set of functions in DAX which generates a table. Some of these functions are from the form of table constructors, I have written about the Table Constructor in DAX, and also the DataTable() function.
There is another set of functions to generate a table. I have written about Calendar() and CalendarAuto() functions and explained how they can be used to create a table with a list of dates. In this article, I am explaining the GenerateSeries() function in DAX and how you can create a table with it.
GenerateSeries is a function in DAX that generates a list of values. The list starts from a Start value and ends at an End value. You can also specify an increment. However, the increment value is optional, and if you don’t set that value, the default increment would be 1.
GenerateSeries(<start value>,<end value>,[increment value])
Sample Usage: Creating List of Numbers
GenerateSeries is a simple function to use, you can easily use it with the expression below:
Sample Table = GENERATESERIES(1,10)
The result would be a table with values from one to ten, incrementing one at a time (the default increment value is one).
Your numbers can be even negative as below;
Sample Table = GENERATESERIES(-3,3)
Changing the Increment Value
You can also change the increment value to whatever you want.
Sample Table = GENERATESERIES(0,10,2)
The start and end value or/and the increment value can be also decimal values.
Sample Table = GENERATESERIES(1.0,3.0,0.4)
You might, however, need to set the number of decimal place characters to the right value to see the effect.
List of Dates
GenerateSeries is not just for numeric values. It also works for date values. Here is an example:
Sample Table = GENERATESERIES( DATE(2019,10,1), DATE(2019,10,15) )
The default increment is one value, which for the data type of DateTime means one day. You can, however, change it to weekly or any other durations with changing the increment:
Sample Table = GENERATESERIES( DATE(2019,10,1), DATE(2019,10,15), 7 )
If you are looking for other ways of creating a list of dates, check out my article about Calendar() and CalnedarAuto() functions in DAX.
List of Times
You can also generate a list of Times, using the same function;
Table = GENERATESERIES( Time(1,0,0), TIME(2,0,0), 1/24/60/60)
Change the increment
However, for the TIME value, you need to set the increment, because the default is one day, and that won’t affect the time over 24 hours! If you want to get to other time portions you can follow this approach:
- 1: means a day: 24 hours
- 1/24: means one hour
- 1/24/60: means one minute
- 1/24/2: means a half hour
- 1/24/60/60: means one second
And you can build all types of other combinations. Below is a table of times, adding one second at each row;
List of Currency Values
Table = GENERATESERIES( CURRENCY(100.30), CURRENCY(110.1), CURRENCY(0.5) )
GenerateSeries is used when you create a What If Parameter in Power BI
When you create a What If Parameter in Power BI, behind the scene, the GenerateSeries function is used:
The What If Parameter is a list of values in DAX which is generated using the GenerateSeries function.
In summary, if you want to create a list of values in Power BI using DAX, GenerateSeries is a great function to do that. It works not only with numeric values but also with date and time values.