Lab - Introduction to DAX in Power BI Desktop

Completed

Access your environment

Before you start this lab (unless you are continuing from a previous lab), select Launch lab above.

You are automatically logged in to your lab environment as data-ai\student.

You can now begin your work on this lab.

Tip

To dock the lab environment so that it fills the window, select the PC icon at the top and then select Fit Window to Machine.

Screenshot of the lab with the PC icon selected and the Fit Window to Machine option highlighted.

In this lab, you will create calculated tables, calculated columns, and simple measures using Data Analysis Expressions (DAX).

In this lab, you learn how to:

  • Create calculated tables

  • Create calculated columns

  • Create measures

Exercise 1: Create Calculated Tables

In this exercise, you will create two calculated tables. The first will be the Salesperson table, to allow a direct relationship between it and the Sales table. The second will be the Date table.

Open the Sales Analysis.pbix starter file, which is found in the D:\DA100\Labs\05-create-dax-calculations-in-power-bi-desktop\Starter folder.

Task 1: Create the Salesperson table

In this task, you will create the Salesperson table (direct relationship to Sales).

  1. In Power BI Desktop, in Report view, on the Modeling ribbon, from inside the Calculations group, click New Table.

    Screenshot of the New Table button.

  2. In the formula bar (which opens directly beneath the ribbon when creating or editing calculations), type Salesperson =, press Shift+Enter, type 'Salesperson (Performance)', and then press Enter.

    Screenshot of the Salesperson new column formula.

    For your convenience, all DAX definitions in this lab can be copied from the D:\DA100\Lab06A\Assets\Snippets.txt file.

    A calculated table is created by first entering the table name, followed by the equals symbol (=), followed by a DAX formula that returns a table. The table name cannot already exist in the data model.

    The formula bar supports entering a valid DAX formula. It includes features like auto-complete, Intellisense and color-coding, enabling you to quickly and accurately enter the formula.

    This table definition creates a copy of the Salesperson (Performance) table. It copies the data only, however properties like visibility, formatting, etc. are not copied.

    Tip

    You are encouraged to enter “white space” (i.e. carriage returns and tabs) to layout formulas in an intuitive and easy-to-read format—especially when formulas are long and complex. To enter a carriage return, press Shift+Enter. “White space” is optional.

  3. In the Fields pane, notice that the table icon has a calculator icon as well (denoting a calculated table).

    Screenshot pointing out the Salesperson icon.

    Calculated tables are defined by using a DAX formula which returns a table. It is important to understand that calculated tables increase the size of the data model because they materialize and store values. They are recomputed whenever formula dependencies are refreshed, as will be the case in this data model when new (future) date values are loaded into tables.

    Unlike Power Query-sourced tables, calculated tables cannot be used to load data from external data sources. They can only transform data based on what has already been loaded into the data model.

  4. Switch to Model view.

  5. Notice that the Salesperson table is available (take care, it might be hidden from view—scroll horizontally to locate it).

  6. Create a relationship from the Salesperson | EmployeeKey column to the Sales | EmployeeKey column.

  7. Right-click the inactive relationship between the Salesperson (Performance) and Sales tables, and then select Delete.

    Screenshot of delete inactive relationship.

  8. When prompted to confirm the deletion, click Delete.

    Screenshot to confirm deletion.

  9. In the Salesperson table, multi-select the following columns, and then hide them:

    • EmployeeID

    • EmployeeKey

    • UPN

  10. In the diagram, select the Salesperson table.

  11. In the Properties pane, in the Description box, enter: Salesperson related to a sale

    Recall that descriptions appear as tooltips in the Fields pane when the user hovers their cursor over a table or field.

  12. For the Salesperson (Performance) table, set the description to: Salesperson related to region(s).

    The data model now provides two alternatives when analyzing salespeople. The Salesperson table allows you to analyze sales made by a salesperson, while the Salesperson (Performance) table allows you to analyze sales made in the sales region(s) assigned to the salesperson.

Task 2: Create the Date table

In this task, you will create the Date table.

  1. Switch to Data view.

    Screenshot of the data view icon.

  2. On the Home ribbon tab, from inside the Calculations group, click New Table.

    Screenshot of the New table button.

  3. In the formula bar, enter the following, and then press Enter:

     Date =  
     ‎CALENDARAUTO(6)
    

    Screenshot of a new Date formula.

    The CALENDARAUTO() function returns a single-column table consisting of date values. The “auto” behavior scans all data model date columns to determine the earliest and latest date values stored in the data model. It then creates one row for each date within this range, extending the range in either direction to ensure full years of data is stored.

    This function can take a single optional argument which is the last month number of a year. When omitted, the value is 12, meaning that December is the last month of the year. In this case 6 is entered, meaning that June is the last month of the year.

  4. Notice the column of date values.

    If the column does not appear, in the Fields pane, select a different table, and then select the Date table.

    Screenshot of the new created Date table.

    The dates shown are formatted using US regional settings (i.e. mm/dd/yyyy).

  5. At the bottom-left corner, in the status bar, notice the table statistics, confirming that 1826 rows of data have been generated, which represents five full years’ data.

    Screenshot that shows the Date table has 1826 rows.

Task 3: Create calculated columns

In this task, you will add additional columns to enable filtering and grouping by different time periods. You will also create a calculated column to control the sort order of other columns.

  1. On the Table Tools contextual ribbon, from inside the Calculations group, click New Column.

    Screenshot of the New column button.

  2. In the formula bar, type the following, and then press Enter:

     Year =
    
     "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)
    

    A calculated column is created by first entering the column name, followed by the equals symbol (=), followed by a DAX formula that returns a single-value result. The column name cannot already exist in the table.

    The formula uses the date’s year value but adds one to the year value when the month is after June. This is how fiscal years at Adventure Works are calculated.

  3. Verify that the new column was added.

    Screenshot to verify the new column was added.

  4. Use the snippets file definitions to create the following two calculated columns for the Date table:

    • Quarter
    • Month

    Screenshot that shows quarter and month columns have been added.

  5. To validate the calculations, switch to Report view.

  6. To create a new report page, at the bottom-left, click the plus icon.

    Screenshot of the create new report page button.

  7. To add a matrix visual to the new report page, in the Visualizations pane, select the matrix visual type.

    Tip

    You can hover the cursor over each icon to reveal a tooltip describing the visual type.

    Screenshot of available visuals in the Visualizations pane.

  8. In the Fields pane, from inside the Date table, drag the Year field into the Rows well.

    Screenshot that shows how to drag year to the rows field.

  9. Drag the Month field into the Rows well, directly beneath the Year field.

    Screenshot that shows month added to rows.

  10. Next to the matrix visual, click the forked-double arrow icon (which will expand all years down one level).

    Screenshot that shows the forked double arrow button.

  11. Notice that the years expand to months, and that the months are sorted alphabetically rather than chronologically.

    Screenshot that shows months sorted alphabetically.

    By default, text values sort alphabetically, numbers sort from smallest to largest, and dates sort from earliest to latest.

  12. To customize the Month field sort order, switch to Data view.

  13. Add the MonthKey column to the Date table. This formula computes a numeric value for each year/month combination.

     MonthKey =
    
     (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])
    
  14. In Data view, verify that the new column contains numeric values (e.g. 201707 for July 2017, etc.).

    Screenshot of the newly created MonthKey column.

  15. In the Fields pane, ensure that the Month field is selected (when selected, it will have a dark gray background).

  16. On the Column Tools contextual ribbon, from inside the Sort group, click Sort by Column, and then select MonthKey.

    Screenshot that shows how to use the Sort by column.

  17. In the matrix visual, notice that the months are now chronologically sorted.

    Screenshot that proves the months are now sorted correctly.

Task 4: Complete the Date table

In this task, you will complete the design of the Date table by hiding a column and creating a hierarchy. You will then create relationships to the Sales and Targets tables.

  1. Switch to Model view.

  2. In the Date table, hide the MonthKey column.

  3. In the Date table, create a hierarchy named Fiscal, with the following three levels:

    • Year
    • Quarter
    • Month

    Screenshot of the Date hierarchy.

  4. Create the follow two model relationships:

    • Date | Date to Sales | OrderDate
    • Date | Date to Targets | TargetMonth
  5. Hide the following two columns:

    • Sales | OrderDate
    • Targets | TargetMonth

Task 5: Mark the Date table

In this task, you will mark the Date table as a date table.

  1. Switch to Report view.

  2. In the Fields pane, select the Date table (not field).

  3. On the Table Tools contextual ribbon, from inside the Calendars group, click Mark as Date Table, and then select Mark as Date Table.

    Screenshot that shows how to Mark as Date Table.

  4. In the Mark as Date Table window, in the Date Column dropdown list, select Date.

    Screenshot of the Mark as date table dialog box.

  5. Click OK.

    Screenshot of click OK.

  6. Save the Power BI Desktop file.

    Power BI Desktop now understands that this table defines date (time). This is important when relying on time intelligence calculations. You will work with time intelligence calculations in Lab 06B.

    Note

    This design approach for a date table is suitable when you don’t have a date table in your data source. If you have access to a data warehouse, it would be appropriate to load date data from its date dimension table rather than “redefining” date logic in your data model.

Exercise 2: Create Measures

In this exercise, you will create and format several measures.

Task 1: Create simple measures

In this task, you will create simple measures. Simple measures aggregate a single column or table.

  1. In Report view, on Page 2, in the Fields pane, drag the Sales | Unit Price field into the Values section in matrix visual.

    Screenshot of a table that shows Year and Unit Price.

    Recall that in Lab 05A, you set the Unit Price column to summarize by Average. The result you see in the matrix visual is the monthly average unit price.

  2. In the visual fields pane (located beneath the Visualizations pane), in the Values well, notice that Unit Price is listed.

    Screenshot of Unit Price in the Values field.

  3. Click the down-arrow for Unit Price, and then notice the available menu options.

    Screenshot of the available menu options.

    Visible numeric columns allow report authors to decide at report design time how a column will summarize (or not). This can result in inappropriate reporting. Some data modelers do not like leaving things to chance, however, and choose to hide these columns and instead expose aggregation logic defined by measures. This is the approach you will now take in this lab.

  4. To create a measure, in the Fields pane, right-click the Sales table, and then select New Measure.

    Screenshot of New Measure selected.

  5. In the formula bar, add the following measure definition:

     Avg Price = AVERAGE(Sales[Unit Price])
    
  6. Add the Avg Price measure to the Values section in the matrix visual.

  7. Notice that it produces the same result as the Unit Price column (but with different formatting).

  8. In the Values well, open the context menu for the Avg Price field, and notice that it is not possible to change the aggregation technique.

    Screenshot verifying that you can't change the aggregation technique for Avg Price.

  9. Use the snippets file definitions to create the following five measures for the Sales table:

    • Median Price
    • Min Price
    • Max Price
    • Orders
    • Order Lines

    The DISTINCTCOUNT() function used in the Orders measure will count orders only once (ignoring duplicates). The COUNTROWS() function used in the Order Lines measure operates over a table. In this case, the number of orders is calculated by counting the distinct SalesOrderNumber column values, while the number of order lines is simply the number of table rows (each row is a line of an order).

  10. Switch to Model view, and then multi-select the four price measures: Avg Price, Max Price, Median Price, and Min Price.

  11. For the multi-selection of measures, configure the following requirements:

    • Set the format to two decimal places
    • Assign to a display folder named Pricing

    Screenshot of the Pricing folder.

  12. Hide the Unit Price column.

    The Unit Price column is now not available to report authors. They must use the measure you’ve added to the model. This design approach ensures that report authors won’t inappropriately aggregate prices, for example, by summing them.

  13. Multi-select the Orders and Order Lines measures, and configure the following requirements:

    • Set the format use the thousands separator
    • Assign to a display folder named Counts

    Screenshot of the Counts folder.

  14. In Report view, in the Values well of the matrix visual, for the Unit Price field, click X to remove it.

    Screenshot of how to remove Unit Price field.

  15. Increase the size of the matrix visual to fill the page width and height.

  16. Add the following five new measures to the matrix visual:

    • Median Price
    • Min Price
    • Max Price
    • Orders
    • Order Lines
  17. Verify that the results looks sensible and are correctly formatted.

    Screenshot of all measures formatted correctly.

Task 2: Create additional measures

In this task, you will create additional measures that use more complex expressions.

  1. In Report view, select Page 1.

    Screenshot of how to select Page 1.

  2. Review the table visual, noticing the total for the Target column.

    Screenshot that highlights the Target total.

    Summing the target values together doesn’t make sense because salespeople targets are set for each salesperson based on their sales region assignment(s). A target value should only be shown when a single salesperson is filtered. You will implement a measure now to do just that.

  3. In the table visual, remove the Target field.

    Screenshot that shows how to remove Target field.

  4. Rename the Targets | Target column as Targets | TargetAmount.

    Tip

    There are several ways to rename the column in Report view: In the Fields pane, you can right-click the column, and then select Rename—or, double-click the column, or press F2.

    You’re about to create a measure named Target. It’s not possible to have a column and measure in the same table, with the same name.

  5. Create the following measure on the Targets table:

     Target =
    
     IF(
    
     HASONEVALUE('Salesperson (Performance)'[Salesperson]),
    
     SUM(Targets[TargetAmount])
    
     )
    

    The HASONEVALUE() function tests whether a single value in the Salesperson column is filtered. When true, the expression returns the sum of target amounts (for just that salesperson). When false, BLANK is returned.

  6. Format the Target measure for zero decimal places.

    Tip

    You can use the Measure Tools contextual ribbon.

  7. Hide the TargetAmount column.

  8. Add the Target measure to the table visual.

  9. Notice that the Target column total is now BLANK.

    Screenshot that verifies the Target total is blank.

  10. Use the snippets file definitions to create the following two measures for the Targets table:

    • Variance
    • Variance Margin
  11. Format the Variance measure for zero decimal places.

  12. Format the Variance Margin measure as percentage with two decimal places.

  13. Add the Variance and Variance Margin measures to the table visual.

  14. Widen the table visual so all values are displayed.

    Screenshot of all values displayed.

    While it appears all salespeople are not meeting target, remember that the measures aren’t yet filtered by a specific time period. You’ll produce sales performance reports that filter by a user-selected time period in Lab 07A.

  15. At the top-right corner of the Fields pane, collapse and then expand open the pane.

    Screenshot of how to expand the Fields pane.

    Collapsing and re-opening the pane resets the content.

  16. Notice that the Targets table now appears at the top of the list.

    Screenshot that shows the Targets table at the top.

    Tables that comprise only visible measures are automatically listed at the top of the list.

Finish up

In this task, you will complete the lab.

  1. Save the Power BI Desktop file.

  2. Leave Power BI Desktop open.

In the next lab, you will enhance the data model with more advanced calculations using DAX.