GENERATE

Where `❰table❱` is the name of the table you want to iterate over, and `❰values❱` are the columns from that table you want to include in the new table.

Let's take a look at a practical example to better understand how this function works.

Example: Creating a Table with Combinations of Values

Suppose you have a table called "Sales" with the following columns: "Region", "Product", and "Sales Amount". You want to create a new table that shows the total sales by region and product. Here's how you can use the GENERATE function to achieve this:

In this example, we're using the VALUES function to extract the distinct values of the "Region" and "Product" columns from the "Sales" table. The GENERATE function then iterates over these values and creates a new table with all possible combinations of regions and products.

You can then use this new table in your data model to create visualizations, filters, and calculations based on the total sales by region and product.

Example: Creating a Calendar Table

Another common use case for the GENERATE function is to create a calendar table. A calendar table is a table that contains a row for each day between a start and end date. This table is useful for time-based analysis, such as calculating daily, weekly, or monthly averages, or comparing the performance of different periods.

Here's how you can use the GENERATE function to create a calendar table:

In this example, we're using the CALENDAR function to create a list of dates between the minimum and maximum dates in the "Sales" table. We then use a variable called "DateValue" to store the current date value while iterating over the rows.

The RETURN statement creates a new row for each date value, with columns for the date, year, quarter, month, weekday, and day. You can customize this formula to include additional columns or calculations based on your specific needs.

Once you have created the calendar table, you can use it in your data model to create time-based aggregations and comparisons.

The GENERATE function is a powerful tool in the arsenal of any Power BI user. With this function, you can create new tables with combinations of values from existing tables, or create specialized tables for time-based analysis.

In this article, we've covered the basics of the GENERATE function and provided practical examples for you to follow. We hope this will help you unleash the full potential of Power BI and create insightful visualizations and analyses for your business.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us