Lesson 36 Power BI DAX: Mastering Formulas for Data Analysis 📊

Welcome back to Virvijay.com, where we simplify Power BI for you! 🎯

One of the most powerful features of Power BI is DAX (Data Analysis Expressions). It helps you create custom calculations, analyze trends, and unlock deep insights from your data.

By the end of this blog, you’ll learn:

• ✅ What is DAX and why it’s important.
• ✅ How to use basic and advanced DAX formulas.
• ✅ The difference between calculated columns & measures.
• ✅ How to use time intelligence functions for trend analysis.

1️⃣ What is DAX in Power BI?

🔹 DAX (Data Analysis Expressions) is a formula language used in Power BI to create custom calculations.

• 🔹 Similar to Excel formulas, but much more powerful.
• 🔹 Used for data modeling, custom KPIs, and advanced analytics.

📌 Why Use DAX?

• ✔️ Perform advanced calculations beyond basic sum & count.
• ✔️ Create dynamic measures that update automatically.
• ✔️ Use time intelligence functions to analyze trends.
• ✔️ Improve dashboard interactivity with calculated fields.

2️⃣ DAX Syntax: Understanding the Basics

A DAX formula follows this structure:

DAX

Measure Name = Function(Expression)

Example:

DAX

Total Sales = SUM(Sales[Amount])

• 🔹 Total Sales → Name of the measure.
• 🔹 SUM(Sales[Amount]) → Formula that sums the sales amount column.

3️⃣ Calculated Columns vs. Measures

📌 What’s the Difference?

🔹 When to Use a Calculated Column?

• ✔️ When you need a new column in your dataset.
• ✔️ Example: Creating a full name from first and last names.

DAX

Full Name = Customers[First Name] & " " & Customers[Last Name]

🔹 When to Use a Measure?

✔️ When you need aggregations that change based on filters.

✔️ Example: Total Sales measure:

DAX

Total Sales = SUM(Sales[Amount])

🚀 Tip: Use Measures instead of Calculated Columns whenever possible to improve performance!

4️⃣ Common DAX Functions for Power BI

🔹 Aggregation Functions

✔️ SUM(): Adds all values in a column.

DAX

Total Revenue = SUM(Sales[Revenue])

✔️ AVERAGE(): Calculates the average.

DAX

Avg Sales = AVERAGE(Sales[Amount])

✔️ COUNT(): Counts the number of values.

DAX

Total Customers = COUNT(Customers[Customer ID])

🔹 Logical Functions

✔️ IF(): Conditional logic.

DAX

High Sales = IF(Sales[Amount] > 50000, "High", "Low")

✔️ SWITCH(): Replaces multiple IF() conditions.

DAX

Category Type = SWITCH(Sales[Category],  

    "Electronics", "Tech",  

    "Clothing", "Fashion",  

    "Others")

🔹 Filter Functions

✔️ FILTER(): Returns specific rows based on a condition.

DAX

High Revenue Customers = FILTER(Customers, Customers[Revenue] > 100000)

✔️ ALL(): Ignores all filters.

DAX

All Sales = CALCULATE(SUM(Sales[Amount]), ALL(Sales))

✔️ CALCULATE(): The most powerful function for modifying filters.

DAX

Sales Last Year = CALCULATE(SUM(Sales[Amount]), YEAR(Sales[Date]) = 2023)

🚀 Tip: Use CALCULATE() for advanced filtering!

5️⃣ Time Intelligence Functions

📌 Why Use Time Intelligence?

• ✔️ Analyze sales growth over time.
• ✔️ Compare current year vs previous year.
• ✔️ Track monthly trends & seasonality.

🔹 Key Time Intelligence Functions

✔️ TOTALYTD(): Year-to-date sales.

DAX

YTD Sales = TOTALYTD(SUM(Sales[Amount]), Sales[Date])

✔️ PREVIOUSYEAR(): Sales from the previous year.

DAX

Last Year Sales = CALCULATE(SUM(Sales[Amount]), PREVIOUSYEAR(Sales[Date]))

✔️ DATEADD(): Shift time periods (days, months, years).

DAX

Sales 3 Months Ago = CALCULATE(SUM(Sales[Amount]), DATEADD(Sales[Date], -3, MONTH))

🚀 Tip: Combine CALCULATE() with time intelligence functions for custom time comparisons!

6️⃣ Advanced DAX: Running Totals & Moving Averages

🔹 Running Total

Cumulative sales over time.

DAX

Running Total Sales = CALCULATE(SUM(Sales[Amount]),  

    FILTER(ALL(Sales[Date]), Sales[Date] <= MAX(Sales[Date])))

🔹 Moving Average (Last 3 Months)

Tracks trends over time.

DAX

3 Month Avg Sales = AVERAGEX(DATESINPERIOD(Sales[Date], LASTDATE(Sales[Date]), -3, MONTH), SUM(Sales[Amount]))

🚀 Tip: Use AVERAGEX() for rolling averages!

7️⃣ Conclusion: Why DAX is a Game-Changer

DAX allows you to:

• ✅ Perform advanced calculations on your data.
• ✅ Create dynamic measures that update based on filters.
• ✅ Use time intelligence functions for trend analysis.
• ✅ Improve dashboard interactivity & performance.

8️⃣ What’s Next?

📌 In the next blog, you’ll learn:

✅ How to use Power BI Visuals for Storytelling.

✅ How to apply AI Insights in Power BI.

✅ Tips for Power BI Performance Optimization.

🔔 Stay tuned to Virvijay.com for more Power BI tutorials!

💬 Did you find this blog helpful? Share your thoughts in the comments

Write Us support@virvijay.com