Welcome to the world of Data Analysis Expressions (DAX) – the powerful formula language used in Power BI and other Microsoft products. If you’re eager to sharpen your DAX skills, you’re in for a treat! Today, we’ll embark on an exciting journey to solve the first 10 challenges of the “25 Days of DAX Fridays!” challenge from Curbal. You can find the link to the challenge here.

First, it’s essential to take a moment to review one of the most fundamental and versatile functions in DAX – the CALCULATE function. This function will be our trusted companion throughout these challenges, enabling us to modify and control the data context for our calculations.

What is the CALCULATE Function? The CALCULATE function in DAX plays a pivotal role in shaping the way we perform calculations and create measures. Its primary purpose is to modify the filter context for a calculation, allowing us to apply additional filters or change the context in which a measure is evaluated. The key feature of CALCULATE is its ability to dynamically adjust the data used in our calculations, providing flexibility and enabling us to perform advanced analysis.

Basic Syntax: The basic syntax of the CALCULATE function looks like this:

How It Works: The CALCULATE function works by creating a new filter context based on the filters you provide. It evaluates the expression specified in the first argument within this new filter context. Any filters that you provide as arguments to the CALCULATE function will override the existing filters in the original filter context. Also, bear in mind that the FILTER function is often used in combination with CALCULATE to specify the conditions or criteria for filtering the data. Together, these functions provide a lot of flexibility and control over your DAX calculations, making it easier to create meaningful insights and reports from your data.

Now, lets dive in and conquer the first 10 challenges!

Day 1: How many current products cost less than $20?

Day 2: Which product is the most expensive?

Day 3: What is the average unit price for our products?

Day 4: How many products are above the average unit price?

Day 5: How many products cost between $15 and $25?

Day 6: What is the average number of products (not quantity) per order?

Day 7: What is the order value in $ of open (not shipped yet) orders?

Day 8: How many orders are “single item” (only 1 product ordered)?

Day 9: Average sales per transaction for “Romero y Tomillo”?

Day 10: How many days since “North/South” last purchased?

Congratulations! You’ve successfully tackled the first 10th of the “25 Days of DAX Fridays!” challenge. By completing these exercises, you’ve enhanced your DAX skills and become more proficient in Power BI and data analysis. Keep up the great work and stay tuned for the next part, where we’ll conquer the remaining challenges.

Remember, the more you practice, the more confident you’ll become in working with DAX and unleashing the full potential of your data. Happy DAXing!


The Data School
Author: The Data School