📊 How to use Power BI DAX - Tutorial

Introduction

In this tutorial, you will learn how to use DAX (Data Analysis Expressions) in Microsoft Power BI. DAX is essential for creating powerful calculations and aggregations in your reports. Whether you're familiar with Excel formulas or new to data modeling, this guide will walk you through the fundamental steps of using DAX effectively in Power BI.

Step 1: Import Data and Create Data Model

• Open Power BI and select "Get Data."
• Choose your data source (e.g., Excel, SQL Server, etc.) and load the sample data.
• Once imported, navigate to the "Model" view to see your tables.

Step 2: Define Relationships

• Click on the "Manage Relationships" option.
• Create relationships between tables by dragging fields to connect them.
• Ensure that the relationships are set correctly (e.g., one-to-many).

Step 3: Create New Measure Using Sum Function

• Go to the "Data" view and select your desired table.
• In the formula bar, type the following to create a measure:

  Total Sales = SUM(Sales[SalesAmount])
  

• Press Enter to save your measure.

Step 4: Format Measures

• Select the measure you created.
• In the "Modeling" tab, choose the desired format (e.g., currency, percentage).

Step 5: Edit and Delete Measures

• To edit a measure, select it in the "Fields" pane and modify the formula in the formula bar.
• To delete, right-click on the measure and select "Delete."

Step 6: Create New Measure Using Count Function

• Create a new measure to count distinct entries:

  Unique Customers = COUNTROWS(VALUES(Sales[CustomerID]))
  

Step 7: Create New Measure Using Distinct Function

• Use the DISTINCT function as follows:

  Distinct Products = DISTINCTCOUNT(Sales[ProductID])
  

Step 8: Add Comments to Measures

• Add comments within your DAX formulas for clarity:

  Total Sales = 
  // This measure calculates total sales
  SUM(Sales[SalesAmount])
  

Step 9: Create New Measure with Operators

• Combine operations in a measure, for example:

  Profit = SUM(Sales[SalesAmount]) - SUM(Sales[Cost])
  

Step 10: Use Quick Measure Tool

• Right-click on a table and select "New Quick Measure."
• Follow the prompts to create common calculations without writing DAX manually.

Step 11: Create Calculated Column

• In the "Data" view, select a table and click on "New Column."
• Write a formula such as:

  Profit Margin = DIVIDE(Sales[SalesAmount] - Sales[Cost], Sales[SalesAmount])
  

Step 12: Measure vs. Calculated Column

• Understand the differences:
  • Measures are calculations evaluated based on context; ideal for aggregations.
  • Calculated columns are static values added to tables; useful for row-level calculations.

Step 13: Measure that References Another Measure

• Create a new measure that references an existing measure:

  Net Profit = [Total Sales] - [Total Costs]
  

Step 14: Explore Iterator Functions

• Learn and use iterator functions such as:
  • SUMX, COUNTX, AVERAGEX, MAXX, MINX, RANKX
• Example using SUMX:

  Total Revenue = SUMX(Sales, Sales[SalesAmount] * Sales[Quantity])
  

Step 15: Utilize Time and Date Functions

• Use date functions like WEEKDAY:

  Day of Week = WEEKDAY(Sales[OrderDate])
  

Step 16: Implement Find and If Functions

• Use the FIND function to locate text within a string:

  Find Product = FIND("ProductA", Sales[ProductName], 1, 0)
  

• Use the IF function for conditional statements:

  Is High Value = IF(Sales[SalesAmount] > 1000, "Yes", "No")
  

Step 17: Use Calculate Function

• The CALCULATE function modifies the filter context:

  Sales Above 1000 = CALCULATE(SUM(Sales[SalesAmount]), Sales[SalesAmount] > 1000)
  

Conclusion

You have now learned the basics of using DAX in Power BI, from creating measures and calculated columns to utilizing various functions. DAX can significantly enhance your data analysis capabilities, allowing you to create insightful reports. For further learning, consider exploring more advanced DAX functions and features within Power BI. Happy analyzing!