---
title: DAX for Power BI Basic Course (part 1)
tags: pbionline, pbi-day5
---

# Introduction to DAX

:::info
DAX materials for Analyzing Data in Power BI course.

[datapot.vn](datapot.vn), 2021.
:::

<details>
<summary>Revise Data Model</summary>
<br>

:question: What are the common tasks to complete when developing a data model in Power BI?

- Connect to data.
- Transform and prepare data.
- Define business logic by adding DAX calculations.
- Enforce data permissions with row-level security by adding roles.
- Publish the model to Power BI Service.

:question: What are the differences between fact tables and dimension tables?


An example of Star Schema Model


</details>
<br>

**Data Model objects in Power BI** includes:

- Tables
- Columns
- Relationship between tables
- Relationship between columns in a table (hierarchy)
- Measures (calculated by DAX)
- RLS (Row-Level Security)

## DAX Syntax

DAX helps you create new information from data already in your data model. We use DAX to create model object in Power BI data model. With DAX, we can create the following objects:

- Measures
- Columns (called: Calculated Columns)
- Tables (called: Calculated Tables)


<details>
<summary>Explain the formula</summary>

This formula includes the following syntax elements:

A. The measure name, Total Sales.

B. The equals sign operator (=), which indicates the beginning of the formula. When calculated, it will return a result.

C. The DAX function SUM, which adds up all of the numbers in the `Sales[SalesAmount]` column. You’ll learn more about functions later.

D. Parenthesis `()`, which surround an expression that contains one or more arguments. Most functions require at least one argument. An argument passes a value to a function.

E. The referenced table, Sales.

F. The referenced column, `[SalesAmount]`, in the Sales table. With this argument, the SUM function knows on which column to aggregate a SUM.

</details>
<br>

To learn more about DAX syntax, visit this document: [DAX Syntax](https://docs.microsoft.com/en-us/dax/dax-syntax-reference)

> :100: DAX formula can contains one or more functions and references to other model objects. To learn quickly and effective how to write DAX, you need to learn how to use function by understanding which is the output of each function. Example: Some functions return a single value (as measure), while others return a whole table or a column in a table.

## DAX: Calculated table

You can use DAX to create calculated tables from existing tables in your data model. Calculated tables are similar to other tables in your data model, which means you can create relationships, rename its columns, categorize columns' data types, etc.

### Functions for Calculated tables

Some common functions for calculated tables:

DISTINCT
VALUES
CROSSJOIN
UNION
NATURALINNERJOIN
NATURALLEFTOUTERJOIN
INTERSECT
CALENDAR
CALENDARAUTO

:point_right: [Learn all table manupilated functions here](https://docs.microsoft.com/en-us/dax/table-manipulation-functions-dax)

### Example of calculated table

**Case: Create a intermediate tables from two tables**

Imagine you're a personnel manager who has a table of **Northwest Employees** and another table of **Southwest Employees**. You want to combine the two tables into a single table called **Western Region Employees**.

You create calculated tables by using the New table feature in **Report View** or **Data View** of Power BI Desktop.

**Northwest Employees**


**Southwest Employees**


Follow the steps:


Type the following formula in the formula bar:

Western Region Employees = UNION('Northwest Employees', 'Southwest Employees')

**Results:**



### Lab: Create calculated tables and calculate columns

:::warning
We will continue working with AdventureWork Case Study.

Noted that you have to finished Data Model Advanced lab in Day 4 before taking this lab.

Or, you can download the following practice file: [DOWNLOAD PRACTICE FILE](https://datapotanalytic.sharepoint.com/:u:/s/EnlightenmentNow-PowerBITeams/EVqf4qEKxaFCtRX4FTOYiCcBekfkD2b3Vx-yUCszm2PeXw?e=7ljMKS)
:::

#### Task 1: Copy existing table from data model

Requirement:

- Create a Salesperson table to have direct relationship with Sales table.
- Create relationship between the newly created table with the **Sales** table.

<details>
<summary>Solution</summary>
<br>
In Report view, on the Modeling ribbon, inside Calculations group, click New Table.
<br>


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>
<br>

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)

</details>

#### Task 2: Using `CALENDARAUTO` to create a data table

Requirement:

- Create a new table with the following formula (notice that this company has fiscal year ends at 30 June):

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`

<details>
<summary>Solution</summary>
<br>

Select Date table, on the Table Tools contextual tab, inside Calculations group, click New Column.


Create Year column:

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

Create Quarter column:

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:

Month = FORMAT('Date'[Date], "yyyy MMM")

Create MonthKey column:

MonthKey = (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])

</details>

#### Task 3: Simplify the Date table

Requirement:

- 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.

## DAX: Calculated Columns

Calculated columns that are created in Report view or Data view are based on data you've already loaded into the model.

**Common scenarios:**

- Concatenate values from two different columns in two different but related tables.
- Do addition, subtraction from different columns.
- Extract substrings from an existing column.

**Common functions:**

CONCATENATE
YEAR/MONTH/DATE
SWITCH
LEFT
RIGHT

## DAX: Measures

By using measures, you can create some of the most powerful data analysis solutions in Power BI Desktop. Measures help you by performing calculations on your data as you interact with your reports.

There are two type of measures in Power BI:

- Implicit measure (the one that Power BI automatically created for you): are automatic behaviors that allow visuals to summarize model column data.
- Explicit measure (the one that you use DAX to create yourself): are calculations using DAX, and are added to your data model.

### Implicit measure

#### Automatic aggregation functions

Depends on your column data type, Power BI might choose automatic summarization behavior for your column.

<details>
<summary>Summary of Aggregations</summary>

Numeric columns support the greatest range of aggregation functions:
* Sum
* Average
* Minimum
* Maximum
* Count (Distinct)
* Count
* Standard deviation
* Variance
* Median

Text columns allow the following aggregations:

* First (alphabetically)
* Last (alphabetically)
* Count (Distinct)
* Count

Date columns allow the following aggregations:

* Earliest
* Latest
* Count (Distinct)
* Count
* Boolean columns allow the following aggregations:
* Count (Distinct)
* Count

</details>

#### Lab: Implicit measures

**Requirement**: Create some simple charts from your AdventureWork data model to understand how implicit measures work in Power BI.

### Explicit measure

You can choose to define your own DAX measure with a certain aggregation behaviors. These measures called explicit measures.

There are more than 200 DAX functions that you can use. To accelerate your DAX learning progress, it's better to:

- Learn DAX by understanding common DAX patterns, ([daxpattern](https://www.daxpatterns.com) or [sqlbi](http://sqlbi.com))
- Learn DAX by studying DAX functions by group, ([DAX reference](https://docs.microsoft.com/en-us/dax/dax-function-reference))

#### Lab: Explicit measures with simple Aggregations DAX functions

**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)

<details>
<summary>Solution</summary>

Revenue = SUM(Sales[SalesAmount])

Cost = SUM(Sales[Production Cost])

Profit = [Revenue] - [Cost]

Profit Margin = DIVIDE([Profit], [Revenue]) - 1

Avg Price = AVERAGE(Sales[Unit Price])
	
Min Price = MIN(Sales[Unit Price])
	
Max Price = MAX(Sales[Unit Price])
	
Orders = DISTINCTCOUNT(Sales[SalesOrderNumber])
	
Order Lines = COUNTROWS(Sales)

Target = SUM(Targets[TargetAmount])

</details>

### 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.

## Measures or Calculated Columns

Regarding similarities between calculated columns and measures, both are:

- Calculations that you can add to your data model.
- Defined by using a DAX formula.
- Referenced in DAX formulas by enclosing their names within square brackets.

The areas where calculated columns and measures differ include:

- **Purpose** - Calculated columns extend a table with a new column, while measures define how to summarize model data.
- **Evaluation** - Calculated columns are evaluated by using row context at data refresh time, while measures are evaluated by using filter context at query time. Filter context is introduced in a later module; it's an important topic to understand and master so that you can achieve sophisticated summarizations.
- **Storage** - Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
- **Visual use** - Calculated columns (like any column) can be used to filter, group, or summarize (as an implicit measure), whereas measures are designed to summarize.