---
# System prepended metadata

title: Review DAX
tags: [dax-foundation-course, ' pbionline']

---

---
title: Review DAX
tags: dax-foundation-course, pbionline
---
:::info
[datapot.vn](datapot.vn), 2022.
:::

# Review DAX

## Part 1: DAX theories

**Contents**:

- Context in Power BI: Row context and Filter context
- Model objects (mostly) related to DAX: Measures and Calculated Columns
- Common functions: `CALCULATE`, `USERELATIONSHIP`, `FILTER`, `KEEPFILTER`, `REMOVEFILTER`, `VALUE`, Time Intelligence functions, Iterator functions, Aggregation function (`SUM`, `DIVIDE`, `MIN`, `MAX`, etc.)

## Part 2: DAX Practices

Link download practice file: [DAX-Practice](https://datapotanalytic.sharepoint.com/:u:/s/PL300Template/EQcU39k9x-FGk1yjTeNMBuUBBAR98DDS0UCJTfSl0LK0zg?e=K8wzK2)

**AdventureWorks InternetSales**
![](https://hackmd.io/_uploads/SkQxkja45.png)


### I - CALCULATE function
**Question 1**: Calculate % revenue of red bikes to total revenue.

Present the result as follows:
(`Country` field is from `Customer` table)
![](https://hackmd.io/_uploads/SJE_0saV9.png)


**Question 2**: Calculate % revenue of all customer from United States (`Country` column, `Customer` table) or from London (`City` column, `Customer` table) to total revenue.

Present the result as follows:
(`Country` field is from `Customer` table)
![](https://hackmd.io/_uploads/HyIoen649.png)

**Question 3**: Calculate number of orders buying Bikes with `UnitPrice` (`InternetSales` table) less than $1000.
Present the result as follows:

![](https://hackmd.io/_uploads/rJfnInT4c.png)


### II - Conditional functions

**Question 4**: Use "Data Groups" feature, create a new group from `Standard Cost` column, `Product` table, with bin size $100. Create the following visual: 

![](https://hackmd.io/_uploads/r1y0soCNq.png)

(Don't forget to sort columns by `Standard Cost (bins)`)

**Question 5**: Based on the above result, create a new column with `IF` function that classifies `Standard Cost` as:
- Standard Cost < $200 -> "Group 1"
- Standard Cost >= 200 and < 600 -> "Group 2"
- Standard Cost >= 600 and < 1000 -> "Group 3"
- Standard Cost >= 1000 -> "Group 4"

Create another visual to present result:
![](https://hackmd.io/_uploads/BkcYNh04c.png)


**Question 6**: Use `SWITCH` function to solve question 5.


**Comment**: You can also create `Age Group` for Age column in Customer table.

### III - Time Intelligence functions

**Question 7**: Calculate PY Revenue with 3 different ways using the following functions: `SAMPEPERIODLASTYEAR`, `DATEADD`, `PARALLELPERIOD`.

Create the following visual:
![](https://hackmd.io/_uploads/r14TvnREq.png)


**Question 8**: Calculate accumulation of profit margin for the fiscal year of AdventureWork.

![](https://hackmd.io/_uploads/B1GW-aCVc.png)

**Question 9**: Calculate a cummulative revenue that sums up all revenue up to certain date, depends on current filter context to date table.

![](https://hackmd.io/_uploads/B126ya0E5.png)

**Question 10**: Calculate total revenue at the end of each period.

![](https://hackmd.io/_uploads/rJiqMpREc.png)


### IV - Iterator functions

**Question 11**: Calculate average profit and average profit margin per order.

![](https://hackmd.io/_uploads/r15b8T0Eq.png)

**Question 12**: Create a measure to rank product according to their quantity.
![](https://hackmd.io/_uploads/Bkh2Yp0Ec.png)

