Introduction to DAX in Power BI
Introduction to DAX in Power BI
Jess Ahmet
Content Developer, DataCamp
What is DAX?
- Data Analysis eXpressions
- Formula language to create calculations
- Columns, tables, measures
- Based on Excel formulas and functions
- e.g.,
SUM()
- e.g.,
- Used in other Microsoft tools
- Power Pivot and Analysis Services
DAX functions
- Predefined formulas that perform calculations on specific values called arguments
- Function syntax indicates the order of arguments expected
- Function categories
- Aggregation -
SUM(),AVERAGE(),COUNT() - Date and Time -
TODAY(),MONTH(),YEAR() - Logical -
IF(),AND(),OR() - Text -
CONCATENATE(),UPPER(),LEFT() - And many more...
- Aggregation -
- DAX reference:
DAX functions example
SUM()- Syntax:
SUM(<column>) - Description: Adds all the numbers in a column.
- One argument:
<column> - Example:
SUM(Sales)
- Syntax:
LEFT()- Syntax:
LEFT(<text>, <num_chars>) - Description: Returns the specified number of characters from the start of a text.
- Two arguments:
<text>,<num_chars> - Example:
LEFT('DataCamp', 4)= "Data"
- Syntax:
Creating calculated columns
- Expands our existing datasets without editing the source data
- Evaluates at a row level and adds a new column to an existing table
- Calculated at data load or when the data is refreshed
Creating calculated columns
- Expands our existing datasets without editing the source
- Evaluates at a row level and adds a new column to an existing table
- Calculated at data load and when the data is refreshed
- DAX example:
Price_w_tax=Price+ (Price*Tax)
| Item | Price | Tax |
|---|---|---|
| A | $ 20 | 25% |
| B | $ 45 | 0% |
| C | $ 100 | 15% |
Creating calculated columns
- Expands our existing datasets without editing the source
- Evaluates at a row level and adds a new column to an existing table
- Calculated at data load and when the data is refreshed
- DAX example:
Price_w_tax=Price+ (Price*Tax)
| Item | Price | Tax | Price_w_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
| C | $ 100 | 15% | $115 |
Creating calculated measures
- Enables complex calculations
- Aggregates multiple rows and adds a new field that can be added to visualizations
- Calculated at query time as you interact and filter
- More efficient because the calculation is not run every time the table is accessed
- Two ways to create a measure
- Write a measure from scratch
- Use the built-in Quick Measure tool
Creating calculated measures
| Item | Price | Tax | Price_w_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
| C | $ 100 | 15% | $115 |
Total_price_w_tax = SUM(Price_w_tax)Total_price_w_tax = $25 + $45 + $115 = $185
Summary
Calculated columns:
- For evaluating each row
- Add a new column to an existing table
- Calculated at data load or when the data is refreshed
Calculated measures:
Summary
Calculated columns:
- For evaluating each row
- Add a new column to an existing table
- Calculated at data load or when the data is refreshed
| Item | Price | Tax | Price_w_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
| C | $ 100 | 15% | $115 |
Calculated measures:
- For aggregating multiple rows
- Results in another field that you can add to a visualization
- Calculated at query time as you interact and filter
Total_price_w_tax = SUM(Price_w_tax)
Summary
Calculated columns:
- For evaluating each row
- Add a new column to an existing table
- Calculated at data load or when the data is refreshed
| Item | Price | Tax | Price_w_tax |
|---|---|---|---|
| A | $ 20 | 25% | $25 |
| B | $ 45 | 0% | $45 |
| C | $ 100 | 15% | $115 |
Calculated measures:
- For aggregating multiple rows
- Results in another field that you can add to a visualization
Calculated at query time as you interact and filter
Total_price_w_tax = SUM(Price_w_tax)
1 Calculated tables will be covered later.
Adventure Works
- Sells bikes and bike-parts globally
- Table: Sales
- Transactional data for each order line of a sale
- Contains categorical data including product category
Let's practice!
Introduction to DAX in Power BI
