Article 1: Introduction to DAX in Power BI

Welcome to the first article in our series on DAX (Data Analysis Expressions) for Power BI! In this series, we will explore DAX step by step, starting with the basics and gradually diving into more advanced concepts. This first article will introduce you to the fundamental concepts of DAX, its syntax, and how to create a simple data table for practice.

What is DAX?

DAX is a formula language used in Power BI, Excel, and other Microsoft tools for creating custom calculations on data models. It functions similarly to programming languages, allowing you to perform calculations, aggregate data, and manipulate data models effectively.

Understanding DAX is essential for anyone looking to harness the full potential of Power BI and create insightful reports. In this series, we’ll compare DAX to programming constructs to help you grasp its functionality easily.

Creating a Sample Table in DAX

In this article, we will create a sample table called SampleTable_Appliances that contains 100 rows of household appliances with randomly generated prices. We will also define a measure to calculate discounted prices.

Part 1: Creating the Table

To create our sample table, we will use the following DAX code:

// Creating the table SampleTable_Appliances
SampleTable_Appliances = 
ADDCOLUMNS(
    GENERATESERIES(1, 100, 1),
    "Appliance", SWITCH(
        TRUE(),
        [Value] <= 20, "Washing Machine",
        [Value] <= 40, "Refrigerator",
        [Value] <= 60, "Microwave",
        [Value] <= 80, "Air Conditioner",
        "Vacuum Cleaner"  // Default appliance for values above 80
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 20, 300 + RAND() * 200, // Price for Washing Machine
        [Value] <= 40, 500 + RAND() * 300, // Price for Refrigerator
        [Value] <= 60, 100 + RAND() * 50,  // Price for Microwave
        [Value] <= 80, 700 + RAND() * 400, // Price for Air Conditioner
        150 + RAND() * 100                 // Default price for Vacuum Cleaner
    )
)

Explanation of the Table Creation

• ADDCOLUMNS: This function is used to add columns to the table we are creating.
• GENERATESERIES: Generates a series of numbers from 1 to 100.
• SWITCH: Used to determine the appliance type based on the value and to assign prices based on ranges.

Part 2: Defining the Measure

Now that we have our table, we will define a measure called Example1 to calculate the average price of the appliances with a 15% discount:

// Defining the measure Example1
Example1 = 
VAR Discount = 0.15  // Set discount as 15%
VAR OriginalPrice = 
    AVERAGE(SampleTable_Appliances[Price]) // Calculate the average price of appliances
VAR DiscountedPrice = OriginalPrice * (1 - Discount) // Calculate the price with discount
RETURN 
    DiscountedPrice // Return the final discounted price

Explanation of the Measure

• VAR: Defines variables that hold values for calculations.
• AVERAGE: Computes the average price from our sample table.
• RETURN: Returns the calculated discounted price.

Conclusion

In this first article, we introduced DAX and created a sample table with household appliances and their prices. We also defined a measure to calculate discounted prices. This foundational knowledge sets the stage for deeper exploration in the upcoming articles of this series.

Learn More

• Official Microsoft DAX Documentation
• DAX Basics in Power BI
• Power BI Community Forums
• DAX Guide