DAX Fundamentals

DAX Fundamentals: The Secret Language of Power BI 🧙‍♂️

Imagine you have a magical calculator that can answer any question about your data. That’s DAX!

What is DAX?

DAX stands for Data Analysis Expressions. Think of it like a recipe language for Power BI.

You know how a recipe tells you: “Take 2 eggs, add flour, mix, and bake”? DAX is similar. It tells Power BI: “Take this column, add these numbers, and show me the result!”

Simple Example:

Total Sales = SUM(Sales[Amount])

This says: “Add up all the numbers in the Amount column.”

Just like asking your friend: “How much money did we make today?”

DAX Measures vs Columns: The Two Magic Tools 🔧

Imagine you have a toy box (your data table).

🧸 Column Example

A Calculated Column is like putting a sticker on every toy in your box.

Profit = Sales[Revenue] - Sales[Cost]

This creates a new column. Every row gets its own Profit value stored permanently.

When to use: When you need to filter, group, or slice by the new value.

📊 Measure Example

A Measure is like counting toys when someone asks.

Total Profit = SUM(Sales[Profit])

This calculates only when you put it on a chart. It’s not stored anywhere.

When to use: For totals, averages, counts—anything that summarizes data.

Quick Decision Guide:

graph TD
    A["Need a new value?"] --> B{Per row or summary?}
    B -->|Per Row| C["Use Column"]
    B -->|Summary/Total| D["Use Measure"]
    C --> E["Stored in table"]
    D --> F["Calculated on demand"]

DAX Aggregation Functions: The Counting Helpers 🔢

Aggregation functions are like helpful counting robots. Each robot has one job:

🍎 Real-World Example

Imagine a fruit basket:

Total Spent = SUM(Fruits[Price])     → $8
Average Price = AVERAGE(Fruits[Price]) → $2
Fruit Count = COUNTROWS(Fruits)      → 4
Cheapest = MIN(Fruits[Price])        → $1
Most Expensive = MAX(Fruits[Price]) → $3

DAX Filter Functions: The Gatekeepers 🚪

Filter functions are like security guards at a party. They decide who gets in.

CALCULATE: The Boss Guard

CALCULATE is the most powerful function. It changes what data a measure sees.

Sales in USA =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[Country] = "USA"
)

This says: “Add up sales, but ONLY for USA.”

Common Filter Functions:

🎪 Circus Example

Imagine a circus with different acts:

Big Sales Only =
CALCULATE(
    COUNTROWS(Sales),
    FILTER(Sales, Sales[Amount] > 1000)
)

This counts only the “big ticket” performances!

FILTER vs CALCULATE:

graph TD
    A["CALCULATE"] --> B["Changes the context"]
    A --> C["Can use simple filters"]
    D["FILTER"] --> E["Returns a filtered table"]
    D --> F["For complex row conditions"]
    B --> G["Faster for simple cases"]
    E --> H["Use inside CALCULATE"]

Filter Context vs Row Context: The Two Worlds 🌍🌎

This is the secret sauce of DAX. Understanding this makes you a DAX wizard!

🎯 Row Context: Looking at ONE Row

When DAX looks at one row at a time, that’s Row Context.

Where it exists:

• Calculated Columns
• Inside SUMX, AVERAGEX, etc.

Profit Per Item = Sales[Revenue] - Sales[Cost]

DAX goes through each row: “This row’s revenue minus this row’s cost.”

🔍 Filter Context: The Invisible Filter

When you use a chart, slicer, or filter, Power BI creates a Filter Context.

Example: You have a bar chart showing sales by country.

When showing USA’s bar, the Filter Context says: “Only show USA data!”

Total Sales = SUM(Sales[Amount])

Same formula, but the context changes the result!

The Magic Interaction:

graph TD
    A["You create a measure"] --> B["Put it in a visual"]
    B --> C["Visual creates Filter Context"]
    C --> D["Measure calculates within that context"]
    D --> E["Different slice = Different result"]

🎭 The X Functions: Where Both Contexts Meet

Functions ending in X create Row Context inside them:

Weighted Average =
SUMX(
    Products,
    Products[Price] * Products[Quantity]
)

SUMX goes row by row (Row Context) and sums the results.

Quick Comparison:

Putting It All Together 🎉

Let’s see everything work together:

Scenario: Coffee Shop Sales

-- Basic Measure (uses Filter Context)
Total Revenue = SUM(Sales[Amount])

-- With Filter Function
Morning Sales =
CALCULATE(
    SUM(Sales[Amount]),
    Sales[TimeOfDay] = "Morning"
)

-- Percentage of Total (using ALL to ignore filters)
Revenue % =
DIVIDE(
    SUM(Sales[Amount]),
    CALCULATE(SUM(Sales[Amount]), ALL(Sales))
)

-- Using X function (Row Context inside)
Total with Tax =
SUMX(
    Sales,
    Sales[Amount] * 1.1
)

🎓 Key Takeaways

1. DAX = Power BI’s formula language
2. Columns = Stored values, calculated once
3. Measures = Dynamic calculations
4. Aggregations = SUM, AVERAGE, COUNT, MIN, MAX
5. Filter Functions = CALCULATE, FILTER, ALL
6. Row Context = One row at a time
7. Filter Context = The invisible filter from visuals

🚀 You’re Ready!

You now understand the fundamentals of DAX. Like learning to ride a bike, the more you practice, the more natural it becomes.

Remember: Every DAX expert started exactly where you are now. Keep experimenting, keep asking questions, and soon you’ll be creating amazing reports!

“The best way to learn DAX is to write DAX!” ✨