Creating a List of Numbers or Dates in Power BI using GenerateSeries Function in DAX

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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.

Table Generators

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

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.

Sample Usage: Creating List of Numbers

GenerateSeries is a simple function to use, you can easily use it with the expression below:

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;

Changing the Increment Value

You can also change the increment value to whatever you want.

The start and end value or/and the increment value can be also decimal values.

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:

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:

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;

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

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.

Summary

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.

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply

Your email address will not be published. Required fields are marked *