DAX for Beginners Series: Understanding Programming Concepts in DAX – Part 1

DAX (Data Analysis Expressions) is a powerful programming language used in Power BI, Excel, and SQL Server Analysis Services. It allows for advanced calculations and analytics on your data. This is the first part of a 10-article series that will cover key programming concepts applied to DAX, explained step by step with practical examples.

Introduction

In this first article, we will introduce fundamental programming concepts such as variable assignments, conditional logic, and iteration using DAX. Each example will have a different sample table with 100 rows, covering various themes to keep the content dynamic and engaging.

Example 1: Variable Assignment (Appliances)

For this example, let’s create a table of 100 household appliances and their prices. We will assign a 15% discount to each appliance using variables.

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"
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 20, 300 + RAND() * 200,
        [Value] <= 40, 500 + RAND() * 300,
        [Value] <= 60, 100 + RAND() * 50,
        [Value] <= 80, 700 + RAND() * 400,
        150 + RAND() * 100
    )
)

Example1 = 
ADDCOLUMNS(
    SampleTable_Appliances,
    "DiscountedPrice", 
    VAR Discount = 0.15
    VAR OriginalPrice = [Price]
    VAR DiscountedPrice = OriginalPrice * (1 - Discount)
    RETURN DiscountedPrice
)

Explanation:

A sample table of 100 household appliances is generated.
Each appliance has a random price.
A 15% discount is applied using variable assignment.

Example 2: Conditional Logic (`IF`) (Books)

In this example, we create a table of 100 books with different prices. Using conditional logic, we’ll categorize each book as “Cheap” or “Expensive.”

SampleTable_Books = 
ADDCOLUMNS(
    GENERATESERIES(1, 100, 1),
    "BookTitle", SWITCH(
        TRUE(),
        [Value] <= 25, "Fiction",
        [Value] <= 50, "Non-Fiction",
        [Value] <= 75, "Science",
        "Fantasy"
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 25, 10 + RAND() * 5,
        [Value] <= 50, 20 + RAND() * 10,
        [Value] <= 75, 30 + RAND() * 15,
        40 + RAND() * 20
    )
)

Example2 = 
ADDCOLUMNS(
    SampleTable_Books,
    "Category", 
    IF([Price] < 20, "Cheap", "Expensive")
)

Explanation:

A table of books is generated with random prices.
Books are categorized as “Cheap” if their price is below $20, otherwise “Expensive.”

Example 3: Iteration Simulation with `GENERATESERIES` (Smartphones)

This example creates a list of 100 smartphones, and we calculate the square of each phone’s price to demonstrate how iteration can be simulated with GENERATESERIES.

SampleTable_Phones = 
ADDCOLUMNS(
    GENERATESERIES(1, 100, 1),
    "Smartphone", SWITCH(
        TRUE(),
        [Value] <= 25, "iPhone",
        [Value] <= 50, "Samsung",
        [Value] <= 75, "Huawei",
        "OnePlus"
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 25, 700 + RAND() * 300,
        [Value] <= 50, 600 + RAND() * 200,
        [Value] <= 75, 500 + RAND() * 150,
        400 + RAND() * 100
    )
)

Example3 = 
ADDCOLUMNS(
    SampleTable_Phones,
    "PriceSquared", [Price] * [Price]
)

Explanation:

A table of smartphones is generated.
The square of each smartphone’s price is calculated.

Example 4: Using `SWITCH` for Multiple Conditions (Sports Equipment)

Here, we have a table of sports equipment. We adjust the price of each item based on the equipment category using the SWITCH function.

SampleTable_Sports = 
ADDCOLUMNS(
    GENERATESERIES(1, 100, 1),
    "Equipment", SWITCH(
        TRUE(),
        [Value] <= 25, "Tennis Racket",
        [Value] <= 50, "Football",
        [Value] <= 75, "Basketball",
        "Running Shoes"
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 25, 50 + RAND() * 20,
        [Value] <= 50, 30 + RAND() * 10,
        [Value] <= 75, 25 + RAND() * 15,
        100 + RAND() * 50
    )
)

Example4 = 
ADDCOLUMNS(
    SampleTable_Sports,
    "AdjustedPrice", 
    SWITCH(
        TRUE(),
        [Equipment] = "Tennis Racket", [Price] - 5,
        [Equipment] = "Football", [Price] - 3,
        [Equipment] = "Basketball", [Price] - 2,
        [Equipment] = "Running Shoes", [Price] - 10
    )
)

Explanation:

A table of sports equipment with random prices is generated.
The price is adjusted differently for each type of equipment.

Example 5: Sales Tax Calculation (Laptops)

Let’s calculate sales tax for a table of laptops. We’ll assume a 12% tax rate applied to each laptop’s price.

SampleTable_Laptops = 
ADDCOLUMNS(
    GENERATESERIES(1, 100, 1),
    "LaptopBrand", SWITCH(
        TRUE(),
        [Value] <= 25, "Dell",
        [Value] <= 50, "HP",
        [Value] <= 75, "Lenovo",
        "Apple"
    ),
    "Price", SWITCH(
        TRUE(),
        [Value] <= 25, 800 + RAND() * 200,
        [Value] <= 50, 900 + RAND() * 250,
        [Value] <= 75, 1000 + RAND() * 300,
        1500 + RAND() * 500
    )
)

Example5 = 
ADDCOLUMNS(
    SampleTable_Laptops,
    "SalesTax", 
    VAR TaxRate = 0.12
    RETURN [Price] * TaxRate
)

Explanation:

A table of laptops is generated with random prices.
A 12% sales tax is calculated for each laptop.

Learn More

To dive deeper into DAX and Power BI, here are some additional resources:

edvaldo b. guimarães filho

DAX for Beginners Series: Understanding Programming Concepts in DAX – Part 1