DAX for calculated tables and columns
1. DAX for calculated tables and columns
Let's get started with making calculated columns and tables with DAX. I'll be working with the Contoso dataset, which is different from the World Wide Importers data you'll be using in the exercises. The structure of both tables is similar though, they both contain fictitious data of a retail company. There is one main fact table with transactions and different dimensional tables. In this demo, we'll be going through 3 different functions. First, we'll create a calculated table, after which we'll investigate two different DAX expressions to create calculated columns. When looking through the different dimension tables, we notice there is no dimensional date table. Let's use DAX to create it. We navigate to the Table view and select New Table. First, let’s name the table Dim_Calendar. We need to use the CALENDAR function for this exercise. This function creates a range of dates between the two arguments used. Notice Power BI always explains the function you will be using as you are typing. Let’s create a set of dates between the min and max date of the fact table. Notice I pressed Tab here for the full datename to appear automatically. It’s that simple. We already have our date table ready! There is a hierarchy in our new table, so let's move back to the model view and connect it with the fact table, by dragging Date to Datekey. By doing so we established a relationship between both tables. Secondly, let's create a calculated column. All fields in the EntityDescription column in Dim_Entity table start with Contoso. Let's create a shorter version of this column with Contoso removed by using the substitute function. Let's click on new column. We name the column EntityShort. Once we type the function, the explanation pops up again. We enter the column where we want to replace values, entity description, enter the old text Contoso, and the text we want to replace by, a blank in this case. We press enter and see Contoso is now removed in all rows. Thirdly, we'll bring in a column from another table. In the fact table we can only see the key, but sometimes it’s useful to see the actual value it represents as well. The amounts in our fact table are related to a scenario. When investigating the scenario table we see it is an Actual, Budget or Forecast. Let's bring that in. We can do that using the related function. This function returns a corresponding value from another table. Power Bi knows what to do because the relationship between the two tables is already defined. Finally let's visualize the amount by scenario name over time. We navigate to the report pane and create an area chart. We add the date from the table we created to the axis, the scenario name to legend, and amount to values. That way we can compare the amount per scenario over time. Using the drill-down button, we can now see how the forecasted, budgeted, and actual amount compares quarterly or even monthly. That's it for the demo, over to you so you can start using these new functions in the exercises!2. Let's practice!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.