Introduction to DAX in Power BI

In this first article of our 10-part series on DAX (Data Analysis Expressions), we will explore one of the fundamental building blocks of Power BI: DAX formulas. DAX is a powerful formula language designed specifically for working with relational data, performing calculations, and creating dynamic reports. Even if you have little to no experience with DAX or Power BI, by the end of this series, you’ll gain the confidence to build complex data models and insightful reports.

In this article, we’ll break down a simple yet useful DAX formula that creates a dataset of appliance prices. This example will help you understand how DAX can be used to generate series of values, apply conditions, and calculate prices dynamically.

Understanding the Code

Let’s take a closer look at the following DAX code:

AppliancePrices = 
ADDCOLUMNS(
    GENERATESERIES(1, 300, 1),
    "Appliance", SWITCH(
        TRUE(),
        [Value] <= 30, "Refrigerator",
        [Value] <= 60, "Washing Machine",
        [Value] <= 90, "Microwave",
        [Value] <= 120, "Dishwasher",
        [Value] <= 150, "Oven",
        [Value] <= 180, "Air Conditioner",
        [Value] <= 210, "Vacuum Cleaner",
        [Value] <= 240, "Blender",
        [Value] <= 270, "Coffee Maker",
        "Toaster"
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 30, 1200 + RAND() * 100,
        [Value] <= 60, 800 + RAND() * 50,
        [Value] <= 90, 150 + RAND() * 20,
        [Value] <= 120, 700 + RAND() * 50,
        [Value] <= 150, 900 + RAND() * 50,
        [Value] <= 180, 1100 + RAND() * 100,
        [Value] <= 210, 300 + RAND() * 20,
        [Value] <= 240, 100 + RAND() * 10,
        [Value] <= 270, 200 + RAND() * 20,
        50 + RAND() * 5
    )
)

Breakdown of the Code

1. AppliancePrices =
   This part defines the name of the new table or calculated column that we are creating. In this case, we are building a table called AppliancePrices.
2. ADDCOLUMNS
   This function allows us to create a new table by adding calculated columns to an existing table or a series. It takes two main arguments: the existing table or series, and the new columns to be added.
3. GENERATESERIES(1, 300, 1)
   This function generates a series of numbers from 1 to 300, with an increment of 1. This range will represent different rows for each appliance.
4. “Appliance”, SWITCH(TRUE(), …)
   The SWITCH function is used to categorize the generated values into different appliances. It checks each value in the series and assigns it to a specific appliance based on conditions. For example:

• If the Value is less than or equal to 30, it is assigned to a “Refrigerator”.
• If the Value is less than or equal to 60, it becomes a “Washing Machine”, and so on.

1. “Price”, SWITCH(TRUE(), …)
   Similar to how we categorized the appliances, the Price column is generated using another SWITCH function. Based on the value, we assign a price to each appliance type:

• For a “Refrigerator” (where Value <= 30), the price is calculated as 1200 + RAND() * 100, which adds a random component to the base price.
• Other appliances, like “Washing Machine”, “Microwave”, and “Toaster”, have their own price formulas with a random element added to simulate variation.

1. RAND()
   The RAND() function generates a random number between 0 and 1. This function is used to simulate dynamic pricing by adding a random amount to the base price of each appliance.

Step-by-Step Logic

Let’s explain the logic flow:

• We create a series of 300 numbers using GENERATESERIES(1, 300, 1). This will give us 300 rows.
• For each value in this series, we categorize it into one of several appliances using SWITCH. This makes the first 30 values represent “Refrigerators”, the next 30 represent “Washing Machines”, and so on.
• For each appliance, a price is calculated using another SWITCH function. For example, a refrigerator is priced at $1200 plus a small random value, while a toaster is priced much lower at $50 plus a small random addition.

Key Functions and Concepts

• GENERATESERIES: A handy function for generating sequential numbers.
• SWITCH: Used to apply conditions and categorize values.
• RAND: Adds randomness to data, useful for simulating real-world scenarios.
• ADDCOLUMNS: Enables the creation of new tables with calculated columns based on existing data.

Conclusion

This example is a great introduction to the power of DAX in Power BI. You can see how DAX allows you to create custom datasets, apply logical conditions, and perform dynamic calculations, all within a single formula.

In the next article, we will dive deeper into other DAX functions and how they interact to create more complex and insightful data models.

Learn More

To deepen your understanding of DAX and Power BI, here are some helpful resources and tutorials to guide you through the learning process:

1. Power BI Documentation – DAX Basics
   A great place to start learning the basics of DAX in Power BI.
   Official Power BI DAX Basics Documentation
2. Beginner’s Guide to DAX in Power BI
   A tutorial that covers essential DAX concepts for beginners.
   Guide to DAX
3. Microsoft Power BI Guided Learning
   Free interactive courses provided by Microsoft to master Power BI, including DAX.
   Microsoft Power BI Learning Path
4. DAX Guide
   An in-depth reference guide for DAX functions, perfect for quick lookups.
   DAX Guide by SQLBI
5. Power BI Tutorial for Beginners
   A step-by-step tutorial for beginners in Power BI, covering DAX, data visualization, and more.
   Power BI Tutorial
6. Mastering DAX in Power BI – YouTube
   A comprehensive YouTube playlist with tutorials on DAX and Power BI by popular experts.
   Mastering DAX on YouTube