Title: DAX 101: Essential functions to transform your data
Description:
DAX, or Data Analysis Expressions, is the formula language behind Power BI’s powerful analytical capabilities. In this workout, you’ll explore the basics of DAX, understanding its syntax and diving into some essential functions to transform your data analysis in Power BI.
Scenario:
You’re analyzing sales data in Power BI and want to derive some custom metrics. For instance, you wish to calculate the average sales per product, the growth rate month-on-month, and a rolling average for the last three months. How can DAX assist you in creating these custom metrics?
Objectives:
By the end of this workout, you should be able to:
Understand the core principles and syntax of DAX.
Create basic calculated columns and measures using DAX.
Recognize common DAX functions and their applications in data analysis.
Interactive Task:
Given your beginner’s perspective on DAX, answer the following:
If you want to calculate the total sales for a particular product category, how would you frame your DAX formula?
Your Approach: ________________________
How would you use DAX to calculate the month-on-month growth rate in sales?
Your Approach: ________________________
To create a rolling average of sales for the last three months, which DAX function(s) might you consider using?
Your Answer: ________________________
Questions:
In DAX, what is the fundamental difference between a calculated column and a measure?
i) Calculated columns use data, while measures use formulas.
ii) Calculated columns are created at the row level, whereas measures are aggregated.
iii) Calculated columns can only be used in tables, while measures can be used in charts.
iv) There’s no difference; they can be used interchangeably.
Which DAX function would you use to calculate the sum of a particular column in your dataset?
i) SUMMARIZE()
ii) ADD()
iii) TOTAL()
iv) SUM()
Duration: 20 minutes
Difficulty: Beginner
Period:
This workout is released on Tuesday, October 10, 2023, and will end on Friday, October 20, 2023. But you can always come back to any of the workouts and solve them.
In DAX, what is the fundamental difference between a calculated column and a measure?
Answer:
ii) Calculated columns are created at the row level, whereas measures are aggregated.
Which DAX function would you use to calculate the sum of a particular column in your dataset?
Answer:
iv) SUM()
Interactive Task:
If you want to calculate the total sales for a particular product category, how would you frame your DAX formula?
Your Approach: ____
Total Sales for Electronics =
SUMX(
FILTER(Sales, Sales[ProductCategory] = “Electronics”),
Sales[SalesAmount]
)
How would you use DAX to calculate the month-on-month growth rate in sales?
Approach:
First, make sure you have a date table in your data model. If you don’t have one, create a date table with a date column that links to your sales data.
Create a measure that calculates the total sales for the current month. You can use the SUM() function for this. Assuming you have a “Sales” table with a “SalesAmount” column and a date table with a “Date” column, the formula would be:
DAXCopy code
Total Sales This Month = SUM(Sales[SalesAmount])
Create another measure to calculate the total sales for the previous month. You can use the SAMEPERIODLASTYEAR() function to get the sales for the same month in the previous year and then use CALCULATE() to sum the sales for that month. Here’s the formula:
DAXCopy code
Total Sales Last Month =
CALCULATE(
[Total Sales This Month],
SAMEPERIODLASTYEAR('Date'[Date])
)
Calculate the month-on-month growth rate by subtracting the total sales for the previous month from the total sales for the current month and dividing by the total sales for the previous month. Here’s the formula:
DAXCopy code
MoM Sales Growth Rate =
DIVIDE(
[Total Sales This Month] - [Total Sales Last Month],
[Total Sales Last Month],
0
)
This formula calculates the growth rate, and the DIVIDE() function ensures that you handle potential divide-by-zero errors by setting the third argument to 0.
Now, you can use the “MoM Sales Growth Rate” measure in your Power BI reports or any other DAX-compatible tool to display the month-on-month sales growth rate.
To create a rolling average of sales for the last three months, which DAX function(s) might you consider using?
Answer:
Assuming you have a table named “Sales” with columns “Date” and “SalesAmount,” and you want to calculate the rolling average of sales for the last three months:
DAXCopy code
Rolling Average 3 Months =
AVERAGEX(
FILTER(
Sales,
Sales[Date] >= MAX(Sales[Date]) - 83 && Sales[Date] <= MAX(Sales[Date])
),
Sales[SalesAmount]
)
In this formula:
FILTER(Sales, Sales[Date] >= MAX(Sales[Date]) - 83 && Sales[Date] <= MAX(Sales[Date]) filters the “Sales” table to include only the rows where the “Date” falls within the last three months. The MAX(Sales[Date]) function is used to determine the latest date in the dataset.
AVERAGEX() calculates the average of the “SalesAmount” for the filtered rows, giving you the rolling average of sales for the last three months.
This formula considers the dynamic nature of the last three months, so as time progresses, the rolling average will automatically update to include the most recent data.