From the course: Advanced Power BI: DAX Language, Formulas, and Calculations

DAX tables and GENERATESERIES - Power BI Tutorial

From the course: Advanced Power BI: DAX Language, Formulas, and Calculations

Start my 1-month free trial Buy for my team

DAX tables and GENERATESERIES

“

- [Instructor] There are specific functions and DAX that let us create tables using formulas and Power BI. Creating tables with DAX formulas can improve model efficiency especially with dynamic data and ranges. Let's use the generate series DAX function to create a new table in our model containing contiguous values. There are two required inputs for the generate series DAX function and one optional parameter. Our required parameters are the start and end values. We can also add an optional third parameter for the incremental interval between the range determined by the start and end values. And Power BI desktop let's create a new table using the generate series DAX function for all the periods in a mortgage loan. We'll choose to add a new table from the modeling tab. This opens a formula bar at the top of the page. We'll first give this new table the name loan period. We'll then set it equal to the generate series function we'll use to create the table. Let's start the series off at one. For the fixed rate 30 year mortgage loan payments are monthly so we'll want to set up our loan period series so it goes up to 360, which equals 30 years multiplied by 12 months per year. We can add spaces between our parameters after the comma. By default generate series intervals use one as the increment between the values, but we can change that to two, for example through this functions optional third parameter. Because we're going to use 360 monthly periods in our financial model for loans, let's change back this third parameter to one even though we don't need it but we can keep it in the formula as a reference. Notice how this new table has different icon next to it for the loan period than the other table's already in our model. This indicates it's a calculated table instead of one we set up through the power query editor. Your icon might look a bit different than the one we see here, but regardless of the version you're working with, we can tell that this table is different from the others in the model so far. Lastly, we can see the field we added in this new table has the name value in it's one and only column. To make this field name more descriptive, so we can use it in further calculations, let's click into it in the fields pane and change the name value to loan period. To check that the DAX formula created the table that we want it to let's navigate to the date of view and select our loan period table. We can confirm that the calculated table returns what we want and expected to for our model.

Contents