If you understand filter context, you'll understand much more advanced DAX later.

🔹 10. CALCULATE()

One of the most important DAX functions is CALCULATE(). It evaluates an expression after modifying the filter context.

For example:

West Sales = CALCULATE([Total Sales], Customers[Region] = "West")

This calculates sales specifically for the West region.

🔹 11. Why CALCULATE() Is So Important

Many advanced DAX calculations are built around CALCULATE().

It is commonly used for:

• Conditional calculations, Time intelligence, Comparisons, Removing filters, Adding filters, Changing filter context

Learning CALCULATE() properly is one of the biggest milestones in Power BI.

🔹 12. Measures Can Use Other Measures

You don't need to repeat the same logic everywhere.

• Total Sales = SUM(Sales[Sales_Amount])

• Total Cost = SUM(Sales[Cost])

• Total Profit = [Total Sales] - [Total Cost]

This makes your model easier to maintain.

🔹 13. Profit Margin

You can create:

Profit Margin = DIVIDE([Total Profit], [Total Sales])

DIVIDE() is generally safer than manually using "/" because it handles division-by-zero cases more gracefully.

🔹 14. DIVIDE() vs "/"

Instead of [Total Profit] / [Total Sales] prefer:

Profit Margin = DIVIDE([Total Profit], [Total Sales], 0)

You can also specify an alternate result (0) when denominator is zero.

🔹 15. Calculated Column vs Measure — When to Use Which?

Simple rule:

• Use a calculated column when you need a value stored for each row. Examples: Profit per transaction, Customer category, Product classification

• Use a measure when you need an aggregated or dynamically calculated result. Examples: Total Sales, Total Profit, Profit Margin, Average Order Value, Sales Growth %

For most report-level KPIs, measures are usually preferred.

🔹 16. Row Context

Calculated columns work with row context.

For example: Profit = Sales[Sales_Amount] - Sales[Cost]

Power BI evaluates this expression for each row. Think: Row Context = "Which row am I currently calculating?"

🔹 17. Filter Context vs Row Context

• Row Context → Focuses on the current row

• Filter Context → Defines which data is included in a calculation

Simple example: Calculated Column → Row by row. Measure → Based on current filter context.

Understanding this distinction is essential before moving into advanced DAX.

🔹 18. COUNTROWS()

COUNTROWS() counts rows in a table.

Example: Total Orders = COUNTROWS(Sales)

If one row represents one order, this can represent order count. But if your table contains multiple rows per order, it may not. In that situation: Total Orders = DISTINCTCOUNT(Sales[Order_ID]) may be more appropriate.

Always understand the grain of your table.

🔹 19. RELATED()

RELATED() can retrieve a value from a related table when working in row context.

For example: Region = RELATED(Customers[Region])

This can bring the customer's region into a row-level calculation when the relationship and model support it.

🔹 20. Don't Create Everything as a Calculated Column

A common beginner mistake is creating columns for every calculation.

For example: Total Sales, Total Profit, Average Sales, Profit Margin, Sales Growth