--- title: Introduction to DAX - Part 1 (Revision) tags: dax-foundation-course --- :::info DAX materials for Analyzing Data in Power BI course. [datapot.vn](datapot.vn), 2022. ::: **Download practice file**: [LINK DOWNLOAD - Need Login with Datapot account](https://datapotanalytic.sharepoint.com/:u:/s/PL300Template/ER5GarB5qnpLrNIksQZvSxMBck20CuhD3onMFQDwWjJHLg?e=W4Yi7C) ## Obj1: Learn how to create calculated tables #### Task 1: Create a copy of an existing table in the data model **Requirements:** - Create a Salesperson table to have direct relationship with Sales table. - Create relationship between the newly created table with the **Sales** table. **Solutions:** In Report view, on the Modeling ribbon, inside Calculations group, click New Table. <br> ![](https://hackmd.io/_uploads/S1O82u1Et.png) 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**. <br> ![](https://hackmd.io/_uploads/HJfv2dy4Y.png) <br> ![](https://hackmd.io/_uploads/BJKPhdkVt.png) Switch to Model view, then create new relationship between Sales table and Salesperson table (column: EmployeeKey) Hide the following columns in Salesperson table: - EmployeeID - EmployeeKey - UPN (Skip if you do not have this field) #### Task 2: Create a Date table with DAX **Requirement:** - Create a new table with the following formula (notice that this company has fiscal year ends at 30 June): ```python= Date = CALENDARAUTO(6) ``` - Create the calculated columns for new `Date` table: - Year: fiscal year number, example `FY2018` - Quarter: fiscal quarter number, example `2018 Q1` - Month: fiscal month number, example `2018 Jul` - MonthKey: month number, example `201807` **Solutions:** Select Date table, on the Table Tools contextual tab, inside Calculations group, click New Column. ![](https://hackmd.io/_uploads/r16iC_14t.png) Create Year column: ```python= Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1) ``` Create Quarter column: ```python= Quarter = 'Date'[Year] & " Q" & IF(MONTH('Date'[Date]) <= 3, 3, IF(MONTH('Date'[Date]) <= 6, 4, IF(MONTH('Date'[Date]) <= 9, 1, 2))) ``` Create Month column: ```python= Month = FORMAT('Date'[Date], "yyyy MMM") ``` Create MonthKey column: ```python= MonthKey = (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date]) ``` #### Task 3: Simplify the Date table **Requirements:** - Create hierarchy between columns in `Date` table, name it `Fiscal` (Level: Year > Quarter > Month) - Change the default sort behavior of column: For column `Month`, change the feature **Sort by column** (in Column tools tab) from `Month` to `MonthKey`. (Learn more about [Sort one column by another column](https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column)) - Create model relationships: + `Date | Date` to `Sales | OrderDate` + `Date | Date` to `Targets | TargetMonth` - Use feature **Mark as Date table** (in Table tools tab) for your newly created `Date` table. **Solution:** Ask for Datapot supports if you cannot do this task. ## Obj2: Learn how to create calculated columns #### Task 1: Do addition, subtraction from different columns - In **Sales** table, create Profit column from Sales and Cost column ```python= Profit = Sales[Sales] - Sales[Cost] ``` - In **Sales** table, create Profit Margin column from Profit and Sales column ```python= Profit Margin = Sales[Profit]/Sales[Sales] ``` #### Task 2: Create a new conditional column - In **Product** table, create column `Product Segment` to classify product level based on their production cost ```python= Product Segment = SWITCH( TRUE(), 'Product'[Standard Cost] < 300, "Level 1", 'Product'[Standard Cost] < 500, "Level 2", 'Product'[Standard Cost] < 1000, "Level 3", "Level 4") ``` ## Obj3: Learn how to create measures **Requirement**: Create the following measures: - Total Sales Amount (in Sales table), named it `Revenue` - Total Cost (in Sales table), named it `Cost` - Profit, which equals Revenue minus Cost, named it `Profit` - Profit margin, which equals Profit divided by Revenue, named it `Profit Margin` - Group of measures related to Unit Price (in Sales table): + Average price, named it `Avg Price` + Min price, named it `Min Price` + Max price, named it `Max Price` - Count total order, named it `Order Count` - Count total order line, named it `Order Line Count` - Total Sales KPI (column TargetAmount, table Targets), named it `Target` :smile: Hint: Please refer to this document to choose which function to use: [Aggregations DAX function](https://docs.microsoft.com/en-us/dax/aggregation-functions-dax) **Solutions** ```python= Revenue = SUM(Sales[Sales]) ``` ```python= Total Cost = SUM(Sales[Cost]) ``` ```python= Profit = [Revenue] - [Total Cost] ``` ```python= Profit Margin = DIVIDE([Profit], [Revenue]) ``` ```python= Avg Price = AVERAGE(Sales[Unit Price]) ``` ```python= Min Price = MIN(Sales[Unit Price]) ``` ```python= Max Price = MAX(Sales[Unit Price]) ``` ```python= Orders = DISTINCTCOUNT(Sales[SalesOrderNumber]) ``` ```python= Order Lines = COUNTROWS(Sales) ``` ```python= Target KPI = SUM(Targets[Target]) ``` ### Tips: How to organize and manage your DAX measures? As your measures do not attached officially to any tables, when your model extends with hundreds of measures, it's good practice to keep an eye on organizing your DAX measures. **Solution 1:** Using virtual folder model In Model view, choose measures that belong to the same business category (such as Sales measures, Marketing performance measures, etc.). Add these measures to a folder name Sales measures. **Solution 2:** Using measure folder - Create a blank table (Using DAX/or Using Enter Data feature), name it "Facts" - Choose related measures and move it to this blank table. - Hide all the physical columns in the `Facts` table. Result: The `Facts` table will be automatically moved to the first position of your table list.