---
title: DAX for Power BI Basic Course (part 2)
tags: pbionline, pbi-day6
---
# Important DAX Concepts
## Introduction to Context in Power BI
### Lab: Understand context in Power BI
**Requirement 1**: Using the Profit measure created in the previous section, create the following visual:
*Matrix visual: Config the Filter pane s.t it only displays top 5 Salesperson with the highest profit.*
Comment: Each visuals uses the same DAX measure Profit, but with different visual fields, and produces different results.
**Requirement 2**: Interaction between visuals
Comment: Same Profit measure, but when one visual is filtered, other visual show different result.
### `CALCULATE` function
In DAX, `CALCULATE` is the most important and widely use function. Often, we use `CALCULATE` to determine the **filter** condition of a DAX measure.
- Expression will return a scalar value
- Filters can be Boolean expressions or table expressions. Multiple filters are treated as AND logical operator (all filter conditions must be TRUE at the same time).
+ Boolean filter expressions: Only can reference to columns, not measures.
+ Table filter expressions: Filter over a table and return a filtered table for calculation.
### Lab: Practice `CALCULATE` function
**`CALCULATE` with boolean filter**:
```python=
Revenue Red or Blue =
CALCULATE(
[Revenue],
'Product'[Color] IN {"Red", "Blue"}
)
```
```python=
Revenue Expensive Products =
CALCULATE(
[Revenue],
'Product'[List Price] > 1000
)
```
**`CALCULATE` with table filter**:
```python=
High Cost Product Profit =
CALCULATE(
[Profit],
FILTER(
Sales,
[Total Cost] > 1000)
)
```
```python=
High profit margin =
CALCULATE(
COUNTROWS(Sales),
FILTER(Sales, [Profit Margin] > 0.3))
```
```python=
Revenue Red =
CALCULATE(
[Revenue],
FILTER(
'Product',
'Product'[Color] = "Red")
)
```
```python=
Total Sales for 2015 =
CALCULATE(
[Revenue],
YEAR('Sales'[OrderDate]) = 2015
)
```
**Comment**:
- The `CALCULATE` function is your method of creating a DAX measure that will override certain portions of the context that are being used to express the correct result.
- Notice how both measures are now equally the same amount. If you were to filter by any other criteria, including region, employee, or product, the filter context would still be applied to both measures. It's only the year filter that does not apply to that measure.
### Lab: Practice `CALCULATE` function (continue)
**Requirement**: Create the following measures with CALCULATE function:
- Total revenue of United States, named it `Revenue (USA)`
- % Revenue of United Sates compared to Revenue of other countries, named it `% Revenue (USA)`
With these two measures, create the following chart then comment if this chart is effective.
<details>
<summary>Solution to lab CALCULATE function</summary>
```python=
Revenue (USA) =
CALCULATE(
[Revenue],
Region[Country] = "United States")
```
```python=
% Sales (USA) =
DIVIDE([Revenue (USA)], [Revenue])
```
</details>
## Filter context
### Modify context function
We have two common patterns to apply modify context DAX function:
- Pattern: Time Intelligence calculations
- Pattern: Calculate part-to-whole/hierarchical relationship
#### :point_right: Time Intellifence functions
Time intelligence relates to calculations over time. Specifically, it relates to calculations over dates, months, quarters, or years, and possibly time. Rarely would you need to calculate over time in the sense of hours, minutes, or seconds.
In Data Analysis Expressions (DAX) calculations, time intelligence means **modifying the filter context for date filters**.
Common questions answered by using time intelligence functions:
1. What's the accumulation of revenue for the year, quarter, or month?
2. What revenue was produced for the same period last year?
3. What growth in revenue has been achieved over the same period last year?
4. How many new customers made their first order in each month?
5. What's the inventory stock on-hand value for the company's products?
Your model should have a `Date` table satisfies the following conditions:
- It must have a column of data type Date (or date/time), known as the date column.
- The date column must contain unique values.
- The date column must not contain BLANKs.
- The date column must not have any missing dates.
- The date column must span full years. A year isn't necessarily a calendar year (January-December).
- The date table must be indicated as a date table.
**Case 1**: Summarization over time
Common DAX functions:
- **Sum over time**:
+ `TOTALYTD`, `TOTALQTD`, `TOTALMTD`.
- **Filter date then aggregate in `CALCULATE`**:
+ `DATESYTD`, `DATESMTD`, `DATESQTD` as DAX functions for month-to-date (MTD) and quarter-to-date (QTD). You can pass these functions as filters into the CALCULATE DAX function.
- **Filter custom date the aggregate in `CALCULATE`**:
+ `DATESBETWEEN`, `DATESINPERIOD`
**Case 2**: Comparisons over time
Common DAX functions:
- **Shift backward/forward for a specific time periods**: `SAMEPERIODLASTYEAR`, `NEXTDAY`, `NEXTMONTH`, `NEXTQUARTER`, `NEXTYEAR`, and `PREVIOUSDAY`, `PREVIOUSMONTH`, `PREVIOUSQUARTER`, and `PREVIOUSYEAR`.
- **Shift backward/forward for a custom time periods**: `DATEADD`, `PARALLELPERIOD`.
**Lab: Practice Time Intellifence function**
Calculate accumulated revenue from the beginning of the year:
```python=
Renuve YTD = TOTALYTD([Revenue], 'Date'[Date], "6-30")
```
```python=
Revenue PY =
CALCULATE(
SUM(Sales[Sales]),
PARALLELPERIOD(
'Date'[Date], -12, MONTH)
)
```
```python=
Sales YoY Growth =
DIVIDE(
[Revenue],
[Renuve PY]) - 1
```
#### :point_right: Working with part-to-whole relationship
**Pre-requisite**: Before starting this session, make sure that you follow this requirement.
<details>
<summary>Create a matrix visual for DAX practice</summary>
- To configure the matrix visual fields, from the Fields pane, drag the `Region | Regions` hierarchy, and drop it inside the visual.
- Add also the `Revenue` measure.
- To expand the entire hierarchy, at the top-right of the matrix visual, click the forked-double arrow icon twice.
- To format the visual, beneath the Visualizations pane, select the Format pane.
- In the Search box, enter Stepped.
- Set the Stepped Layout property to Off.
- Verify that the matrix visual has four column headers.
(*Due to data refresh issue, your matrix individual values result might look different from that of the picture*)
</details>
We need to create % of revenue for each individual region, country, group in comparison to the whole region, country, and group. In this session, we will learn the following DAX modify filter function:
```
REMOVEFILTERS
```
**Calculate total revenue of all region in the world**:
```python=
Revenue All Region =
CALCULATE([Revenue], REMOVEFILTERS(Region))
```
**Calculate the percentage revenue of individual region to all region in the world**:
```python=
Revenue % All Region =
DIVIDE(
[Revenue],
[Revenue All Region]
)
```
**Calculate the percentage revenue of individual region to all other regions in a particular country**:
```python=
Revenue % Country =
DIVIDE(
[Revenue],
CALCULATE(
[Revenue],
REMOVEFILTERS(Region[Region])
)
)
```
**Calculate the percentage revenue of individual country to all other countries in a group**:
```python=
Revenue % Group =
DIVIDE(
[Revenue],
CALCULATE(
[Revenue],
REMOVEFILTERS(
Region[Region],
Region[Country])
))
```
**Task**: Drag and drop all the measures above to your pre-created matrix at the beginning of this session to see how these measures work in matrix context.
### Verify context function
Sometimes, we need to control how each visual is displayed to business users to maximize their experience in using our report as well as reduce risk of `BLANK()` or non-sense value showing in our report when users interacts with the report.
You will practice the following verify-context function:
```
HASONEVALUE
ISINSCOPE
VALUES
```
<details>
<summary>HASONEVALUE to remove unnecessary value in a visual</summary>
- Rename the `Targets | Target` column as `Targets | TargetAmount`.
- Create a `Target` amount measure:
```python=
Target = SUM(Targets[TargetAmount])
```
- In the Report view, create a table visual as follows:
**Comment**: The Total Target value in the matrix does not have meaning and sometimes might cause confusion. We want this field does not display Total value while others in the same matrix still do.
- Modify the `Target` measure with `HASONEVALUE` function as follows:
```python=
Target =
IF(
HASONEVALUE('Salesperson(Performance)'[Salesperson]),
SUM(Targets[TargetAmount])
)
```
**Comment**: 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.
- Hide the TargetAmount column.
- Add the Target measure to the matrix visual.
- With `HASONEVALUE` function, continue create two measures for the Targets table:
```python=
Variance =
IF(
HASONEVALUE('Salesperson (Performance)'[Name]),
SUM(Sales[Sales]) - [Target]
)
```
```python=
Variance Margin = DIVIDE([Variance], [Target])
```
- Format measures:
+ Variance: zero decimal places
+ Variance Margin: percentage with two decimal places
- Add Variance and Variance Margin to the table visual:
</details>
<details>
<summary>ISINSCOPE to check if the current filter context contains the pre-defined fields</summary>
We will find ways to improve how the original matrix at the **modify filter context lab** displays, using `ISINSCOPE` function. This function check the current filter context (if it contains our pre-defined fields, then return the result `TRUE/FALSE`).
Modify your original measures as follows:
**Calculate the percentage revenue of individual region to all other regions in a particular country**:
```python=
Revenue % Country =
IF(
ISINSCOPE(Region[Region])
DIVIDE(
[Revenue],
CALCULATE(
[Revenue],
REMOVEFILTERS(Region[Region]))
))
```
**Calculate the percentage revenue of individual country to all other countries in a group**:
```python=
Revenue % Group =
IF(
ISINSCOPE(Region[Region])|| ISINSCOPE(Region[Country]),
DIVIDE(
[Revenue],
CALCULATE(
[Revenue],
REMOVEFILTERS(Region[Region], Region[Country])
)))
)
```
**Comment**: `ISINSCOPE` function is used to test whether the region column is the level in a hierarchy. When the region column is not in scope then this measure returns `BLANK`.
</details>
<details>
<summary>VALUES - a more elegant way to modify filter context with CALCULATE function</summary>
`VALUES` function determines what values are in filter context.
- **Table reference**: Returns a table object with the same columns, but only rows in the current filter context.
- **Column reference**: Return a single-column table of unique values that are in current filter context.
**Comment**: `VALUES` function always return a table. Therefore, in order to test if a specific value exist in a certain filter context, your function first has to test that the VALUES() function return a single row.
**Lab**: Calculate sales commission
At Adventure Works, the commission rate is 10 percent of revenue for all countries except the United States. In the United States, salespeople earn 15 percent commission.
Create the following measures:
```python=
Sales Commission =
[Revenue]
* IF(
HASONEVALUE('Region'[Country]),
IF(
VALUES('Region'[Country]) = "United States",
0.15,
0.1
)
)
```
*(Due to data refresh, your data might be different from the above image.)*
**Task**: Re-calculate your `CALCULATE` measures in previous `CALCULATE` function practice session.
</details>
## Row context
:::info
In this section, we will use Internet Sales Model of AdventureWork to practice.
The practice file can be downloaded [here](https://datapotanalytic.sharepoint.com/:u:/s/EnlightenmentNow-PowerBITeams/EcOvEBi6IcNLnnRwYejYECkBiWkie6z7CIPWRxYTFhsjdA?e=wgZ3K4).
:::
Your Power BI visualization and the corresponding DAX measures/calculated columns/calculated tables can be affected filters applied in that visualization or formulas, by relationships between tables. These filters determine the context for dynamic analysis in your report.
Row context relates directly to "the current row". If you have created a calculated column, the row context consists of the values in each individual row and values in columns that related to the current row.
There are two types of calculation that use row context:
- Create a new calculated column
- Create a new meaure using **iterator functions**
### Type 1: Create a calculated column
If you create a formula in a calculated column, the row context for that formula includes the values from all columns in the current row. In case the table is related to another table, the row context also includes all the value from that other table that are related to the current row.
<details>
<summary>Example of calculated column with row context</summary>
In previous section, we learned how to create Date table using `CALENDARAUTO()` function, in which we create the following additional columns:
```python=
Year = "FY" & YEAR('Date'[Date]) + IF(MONTH('Date'[Date]) > 6, 1)
```
```python=
Quarter =
'Date'[Year] & " Q"
& IF(MONTH('Date'[Date]) <= 3, 3,
IF(MONTH('Date'[Date]) <= 6, 4,
IF(MONTH('Date'[Date]) <= 9, 1, 2)))
```
```python=
Month = FORMAT('Date'[Date], "yyyy MMM")
```
```python=
MonthKey = (YEAR('Date'[Date]) * 100) + MONTH('Date'[Date])
```
</details>
<br>
**Comment:** Row context using in DAX formula does not automatically extends beyond the table. If your formula needs to reference columns in other tables, you have two options:
- If the tables are related (directly or indirectly), you can use:
+ `RELATED`: this function retrieves the value at the one-side of the relationship.
+ `RELATEDTABLE`: this function retrieves values on the many-side of the relationship. It returns a table object.
- If the tables are not related, you can use `LOOKUPVALUE` function. This is not a good practice since this function potentially harm your DAX performance.
#### Lab: Row context in Calculated column
**Requirement**: AdventureWorks decided to launch a discount promotion for internet customer, and apply to all order after 01/01/2020. Create a new column to calculate discount amount for each order item satisfies the promotion.
*Hint: Discount Amount equals Quantity (Internet Sales table) Product List Price (Product table) Discount Percentage (Promotion table)*
<details>
<summary>Solution</summary>
<br>
```python=
Discount Amount =
IF(
'Internet Sales'[OrderDate] >= date(2020, 1, 1),
'Internet Sales'[OrderQuantity] *
RELATED('Product'[ListPrice]) *
RELATED(Promotion[DiscountPct]),
0
)
```
</details>
### Type 2: DAX measure with iterator functions
DAX includes functions that iterate calculations over a table. These functions can have multiple current rows and current row contexts. In programming terms, you can create formulas that recurse over an inner and outer loop.
<details>
<summary>A simple example of iterator function</summary>
In Internet Sales table, create the following measures and compare its results.
```python=
Revenue = SUM('Internet Sales'[SalesAmount])
```
```python=
Revenue (X) =
SUMX(
'Internet Sales',
'Internet Sales'[SalesAmount])
```
</details>
Iterator functions in DAX are functions that ended with X (`SUMX`, `AVERAGEX`, `MAXX`, `MINX`, etc.) These functions iterate through all rows of a given table, apply the expression, then aggregate the result.
There are two scenarios that iterator functions might be useful:
- Create a measure with complex summarization.
- Create a measure operates on higher grain summarization.
#### Lab: Row context with Iterator functions
**Complex summarization**: The discount amount calculated column can be written in measure as follows:
```python=
Discount =
CALCULATE(
SUMX(
'Internet Sales',
'Internet Sales'[OrderQuantity] * RELATED('Product'[ListPrice]) * RELATED(Promotion[DiscountPct])),
'Internet Sales'[OrderDate] > date(2020, 1, 1))
```
**Higher grain summarization**: Create the following measure to calculate average revenue:
```python=
Revenue Avg (after discount) =
AVERAGEX(
'Internet Sales',
'Internet Sales'[SalesAmount] - 'Internet Sales'[Discount Amount])
```
:question: What is the problem of the measure above?
<details>
<summary>Correct average revenue measure solution</summary>
```python=
Revenue per Order (after discount) =
AVERAGEX(
VALUES('Internet Sales'[SalesOrderNumber]),
[Revenue] - [Discount])
```
**Comment**: The formula uses the VALUES DAX function which lets your formulas determine what values are in filter context. In this case, this AVERAGEX function iterates over each sales order in filter context. In other words, it iterates over each sales order for the month.
</details>
<br>
:100: (Bonus) Working with RANKX function
<details>
<summary>Click to extend bonus content</summary>
**Note:**
- **Order direction**: ascending or descending. Default value is descending order.
- **Handle ties**: can skip rank values or use dense rank for item with the same value (therefore the same rank).
**Requirement**: Create a measure that ranks product by its sales quantity.
```python=
Quantity = SUM('Internet Sales'[OrderQuantity])
```
```python=
Product Quantity Rank =
RANKX(
ALL('Product'[Product]),
[Quantity]
)
```
```python=
Product Quantity Rank (dense) =
RANKX(
ALL('Product'[Product]),
[Quantity],
,
,
DENSE
)
```
</details>
### Context transition (advanced)
**Objective**: To combine row context and filter context.
**Problem**: Create a calculated column definition in the Customer table to classify customers into a loyalty class. When the revenue that is produced by the customer is less than USD 2500, the customer is classified as Low; otherwise they're classified as High.
- Try to create the following column in `Customer` table:
```python=
Customer Segment = IF(SUM('Internet Sales'[SalesAmount]) < 2500, "Low", "High")
```
- Create a pie chart to present the Revenue by Customer Segment.
**Comment**: The Customer Segment column does not work as expected. Why does it happen and how can we fix it?
<details>
<summary>Solution</summary>
Modify the Reseller Segment as follows, using the measure `Revenue` instead of function `SUM`
```python=
Customer Segment =
IF(
CALCULATE(SUM('Internet Sales'[SalesAmount])) < 2500,
"Low", "High")
```
</details>
## Optional Contents
:100: **(Optional): Fix the Sales Commission measure in previous session lab.**
<details>
<summary>Click to view the Bonus content</summary>
Modify your `Sales Commission` as follows:
```python=
Sales Commission =
SUMX(
VALUES('Region'[Country]),
CALCULATE(
[Revenue]
* IF(
VALUES('Region'[Country]) = "United States",
0.15,
0.1
)
))
```
Result:
</details>
### (Optional) DAX variables
Variables offer several benefits:
- Improving the readability and maintenance of your formulas.
- Improving performance because variables are evaluated once and only when or if they're needed.
- Allowing (at design time) straightforward testing of a complex formula by returning the variable of interest.
:spiral_note_pad: **Re-write all measures in previous session in the form of VAR-RETURN pattern.**
### (Optional) Use relationship effectively
<details>
<summary>How to manipulate relationship in data model using DAX?</summary>
**Practice file**: Resellers Sales Model of AdventureWork.
In this example, you have to open Power Query Editor and choose 2 additional columns in Sales table:
- DueDate
- ShipDate
In the Model view, create relationships between Sales table and Date table:
- Sales | DueDate to Date | Date
- Sales | ShipDate to Date | Date
**Task 1**: Calculate total sales amount that shipped, and total sales amount that due.
```python=
Sales (Shipped) =
CALCULATE(
SUM(Sales[Sales]),
USERELATIONSHIP('Date'[Date], Sales[ShipDate]))
```
```python=
Sales (Due) =
CALCULATE(
SUM(Sales[Sales]),
USERELATIONSHIP('Date'[Date], Sales[DueDate]))
```
**Task 2**: Fix Sales Shipped measure
```python=
Sales Shipped =
CALCULATE(
SUM(Sales[Sales]),
USERELATIONSHIP('Date'[Date], Sales[ShipDate]),
FILTER(
'Date',
'Date'[Year] <> BLANK())
)
```
</details>
### (Optional) Calculate semi-additive fact measure
<details>
<summary>Inventory Balance</summary>
**Problem**: You are performing inventory counts in a warehouse. On Monday, you have 100 mountain bikes, and on Tuesday you have 125 mountain bikes, you wouldn't want to add those together to indicate that you had 225 mountain bikes between those two days.
In this circumstance, if you want to know your stock levels for March, you would need to tell Power BI not to add the measure but instead take the last value for the month of March and assign it to any visual.
```python=
Last Inventory Count =
CALCULATE (
SUM ('Warehouse'[Inventory Count]),
LASTDATE ( 'Date'[Date] ))
```
</details>
### (Optional) Disconnected tables with `SWITCH` function
Refer to: [DAX - SWITCH function](https://hackmd.io/@linhbuimai/Bk_u8D1EK)
# Some notes on DAX
## 1. DAX References
DAX Documentation:
- [DAX function reference, MS source](https://docs.microsoft.com/en-us/dax/dax-function-reference)
- [DAX resource center](https://social.technet.microsoft.com/wiki/contents/articles/1088.dax-resource-center.aspx)
- [Common DAX Scenarios](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg399143(v=sql.110))
- [Overview of DAX by MS with SQLServer](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg399181(v=sql.110))
- [DAX patterns](https://www.daxpatterns.com)
Other:
- [Power BI enterprise good and best practices](https://sqlserverbi.blog/wp-content/uploads/2019/09/power-bi-enterprise-good-and-best-practices.pdf)
- [DAX Learning Path from MS Learning Center](https://docs.microsoft.com/en-us/learn/paths/dax-power-bi/)
- [Handling BLANK value in DAX](https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/gg413463(v=sql.110)#handling-of-blanks-empty-strings-and-zero-values)
- [Pareto chart in Power BI](https://powerbi.tips/2016/10/pareto-charting/)
:question: [How to hide future dates for date-time calculation?](https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/)
## 2. Best Practices
- [Recommended practices for new DAX users](https://powerbi.microsoft.com/fr-fr/blog/4-recommended-practices-for-new-dax-users/)
- [Learn how to use different DAX functions in Videos](https://curbal.com/blogglossary)
- [DAX calculation in Power BI best practices](https://blog.enterprisedna.co/dax-calculations-in-power-bi-best-practices/)
- [Top 10 Power BI mistakes and their best practice solutions](https://www.elegantbi.com/post/top10bestpractices)
- [DAX best practices](https://powerdax.com/dax-best-practices/)
- [DAX best practices](https://maqsoftware.com/insights/dax-best-practices)
### Four recommended practices for new DAX-users
[Source](https://powerbi.microsoft.com/fr-fr/blog/4-recommended-practices-for-new-dax-users/)
**Worry about the important functions first**
**Skip memorizing and focus on understanding concepts**
**Keep your functions as readabale as possible**
**Sort your measures by category**
Sign in with Wallet
Connect another wallet