---
title: Summary DAX basic concept and functions (Day 7)
tags: powerbi, dax, powerbi-course
---

## 1. Basic measures in Power BI

Two types of measures:
- Implicit measures are automatic behaviors that allow visuals to summarize model column data
- Explicit measures, also known simply as measures, are calculations that you can add to your model

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

**Practice**: Create simple measures as follows

Revenue = SUM(Sales[Sales Amount])
Cost = SUM(Sales[Total Product Cost])
Profit = SUM(Sales[Profit Amount])
Quantity = SUM(Sales[Order Quantity])
Minimum Price = MIN(Sales[Unit Price])
Maximum Price = MAX(Sales[Unit Price])
Average Price = AVERAGE(Sales[Unit Price])

# Count Order line and Order
Order Line Count = COUNT(Sales[SalesOrderLineKey]) 
# --> should use COUNTROW for better performance
Order Line Count = COUNTROWS(Sales)

Order Count = DISTINCTCOUNT('Sales Order'[Sales Order])

**Compare calculated columns and measures:**
- SIMILARITIES:
- 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.
- DIFFERENCES
- 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.

## 2. Overview of context in Power BI

- Review filter, slicer (visualization)
- Types of context: row context, filter context

## 3. Row context
Row context: means the current row, often used in calculated column.

# Create the following measure
Revenue = SUM(Sales[Sales Amount])

Revenue (MC) = 
// Manual Calculation Revenue, equivalent to SUM(Sales[Sales Amount])
SUMX(
	Sales,
	Sales[Order Quantity] * Sales[Unit Price] 
    * (1 - Sales[Unit Price Discount Pct])
)

Discount = 
SUMX(
	Sales,
	Sales[Order Quantity] 
    * (RELATED('Product'[List Price]) - Sales[Unit Price])
)

# Create the following columns (in Sales table)
Discount Amount = (Sales[Order Quantity] * RELATED('Product'[List Price])) 
                  - Sales[Sales Amount]

Takeaway:
- Notice that the formula uses the `RELATED` function. Remember, row context does not extend beyond the table. If your formula needs to reference columns in other tables, and model relationships exist between the tables, use the `RELATED` function for the one-side relationship or the `RELATEDTABLE` function for the many-side relationship.

**Practice: Calculate average revenue**

Revenue Avg Order Line =
AVERAGEX(
	Sales,
	Sales[Order Quantity] * Sales[Unit Price] 
    * (1 - Sales[Unit Price Discount Pct])
)

# Increate the granuality to the sales order level:
Revenue Avg Order =
AVERAGEX(
	VALUES('Sales Order'[Sales Order]),
	[Revenue]
)

Takeaway:
- Use `VALUES` DAX function, this function 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. Filter context and the VALUES function are introduced in the filter context module.
- Iterator functions are used in 2 scenarios: Complex summarizations; Higher grain summarization

**Practice: Create ranking measures, also create a slicer with Category**

# Create the following measures:
"Rank without dense"
Product Quantity Rank =
RANKX(
	ALL('Product'[Product]),
	[Quantity]
)

"Rank with dense: No missing rank number should exist"
Product Quantity Rank (dense) =
RANKX(
	ALL('Product'[Product]),
	[Quantity],
	,
	,
	DENSE
)

"Rank measure which return BLANK unless a single product is filtered"
Product Quantity Rank =
IF(
	HASONEVALUE('Product'[Product]),
	RANKX(
		ALL('Product'[Product]),
		[Quantity],
		,
		,
		DENSE
	)
)

## 4. Filter context

Filter context: describes the filters that are applied during the evaluation of a measure or measure expression. For example:
- Filter applied directly in a column
- Filter applied indirectly through a column from related table
- Filter applied during report design time: through filter pane or slicer (3 levels of filters: visual/page/report)
- Filter applied when report users interact with reports (cross-filter, cross-highlight visuals)

**The key to writing complex measures is mastering these concepts:**

- Understanding how filter context works
- Understanding when and how to modify filter context to achieve a required result
- Composing a formula to accurately and efficiently modify filter context

### **Modify Filter context**

CALCULATE(<expression>, [[<filter1>], <filter2>]…)

- Expression: measure /or any expression that can be used in filter context
- Filters: Boolean expression or Table filter expression (BUT: All filter expressions that are passed in to the CALCULATE function are table filter expressions.)
- Multiple filters mean: AND

**Practice: Create the following measure**

# Apply Boolean expression filter in Calculate function
Revenue Red = CALCULATE([Revenue], 'Product'[Color] = "Red")
Revenue Red or Green = CALCULATE([Revenue], 'Product'[Color] IN {"Red", "Green"})
Revenue Expensive Products = CALCULATE([Revenue], 'Product'[List Price] > 1000)

# Apply table expression filter in Calculate function
Revenue High Margin Products =
CALCULATE(
	[Revenue],
	FILTER(
		'Product',
		'Product'[List Price] > 'Product'[Standard Cost] * 2
	)
)

# Comment: The filter table of above expression 
# is the subset of its original table

"Similar expression as follows:"
Revenue Red =
CALCULATE(
	[Revenue],
	FILTER(
		'Product',
		'Product'[Color] = "Red"
	)
)

### **Use filter modifier functions**

Filter functions can:
- Add filter conditions to expression
- Modify/replace current filter condition in expression

**Example: `REMOVEFILTER`, similar to `ALL`, `ALLEXCEPT`, `ALLNOBLANKROW` (used to remove filter in the current expression)**

Revenue Total Region = CALCULATE([Revenue], REMOVEFILTERS('Sales Territory'))

Revenue % Total Region =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalRegionRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS('Sales Territory')
	)
RETURN
	DIVIDE(
		CurrentRegionRevenue,
		TotalRegionRevenue
	)
    
Revenue % Total Country =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalCountryRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS('Sales Territory'[Region])
	)
RETURN
	DIVIDE(
		CurrentRegionRevenue,
		TotalCountryRevenue
	)
    
Revenue % Total Group =
VAR CurrentRegionRevenue = [Revenue]
VAR TotalGroupRevenue =
	CALCULATE(
		[Revenue],
		REMOVEFILTERS(
			'Sales Territory'[Region],
			'Sales Territory'[Country]
		)
	)
RETURN
	DIVIDE(
		CurrentRegionRevenue,
		TotalGroupRevenue
	)

**Example: Reserves Filter using `KEEPFILTERS`**

"Compare the following measures:"
Revenue Red (boolean) = CALCULATE([Revenue], 'Product'[Color] = "Red")
Revenue Red =
CALCULATE(
	[Revenue],
	KEEPFILTERS('Product'[Color] = "Red")
)

### **Use Inactive Relationship**

Revenue Shipped =
CALCULATE (
	[Revenue],
	USERELATIONSHIP('Date'[DateKey], Sales[ShipDateKey])
)

- `CROSSFILTER`: used to modify filter directions (from both to single or from single to both) and even disable a relationship.

## 5. Time Intelligence Functions

**Common questions:**
* What's the accumulation of revenue for the year, quarter, or month?
* What revenue was produced for the same period last year?
* What growth in revenue has been achieved over the same period last year?
* How many new customers made their first order in each month?
* What's the inventory stock on-hand value for the company's products?

**Date table requirements:**
* 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.

[How to create datetime table in Power BI Desktop](https://docs.microsoft.com/en-us/power-bi/guidance/model-date-tables/)

### **Scenario 1: Summarizations over time**

Common functions:
- Group 1: DATESYTD, DATESQTD, DATESMTD (passed in CALCULATE function)
- Group 2: TOTALYTD, TOTALQTD, TOTALMTD
- Group 3: DATESBETWEEN, DATESINPERIOD

> While the TOTALYTD function is simple to use, you are limited to passing in one filter expression. If you need to apply multiple filter expressions, use the CALCULATE function and then pass the DATESYTD function in as one of the filter expressions.

Revenue YTD = TOTALYTD([Revenue], 'Date'[Date], "6-30")

# Similarly, create Revenue YTD with other functions in group 1, 3

### **Scenario 2: Comparisons over time**

(Shifting time periods)

- DATEADD, PARALLELPERIOD, SAMEPERIODLASTYEAR
- NEXTDAY , NEXTMONTH , NEXTQUARTER , NEXTYEAR , and PREVIOUSDAY , PREVIOUSMONTH , PREVIOUSQUARTER , and PREVIOUSYEAR

Revenue PY =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
	RevenuePriorYear
    
Revenue YoY % =
VAR RevenuePriorYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
	DIVIDE(
		[Revenue] - RevenuePriorYear,
		RevenuePriorYear
	)