DEV Community

Cover image for Stop Googling DAX Formulas. Here are the 5 I Actually Use to Solve Business Problems.
Mohamed Abdelaziz
Mohamed Abdelaziz

Posted on • Originally published at mohamedabdelaziz.dev

Stop Googling DAX Formulas. Here are the 5 I Actually Use to Solve Business Problems.

#powerbi #businessintelligence #dax #dataanalytics

If you are just starting with Power BI, DAX can feel like a scary foreign language.

I remember staring at the screen, trying to memorize hundreds of functions, thinking I needed to know them all to be good at my job.

But here’s the truth: In business analytics, you don’t need a hundred formulas. You need about five, used in the right way.

These are the 5 DAX functions that solve 80% of the real-world business problems I face. Let me show you how I use them with simple examples.

1. CALCULATE

This is the king. It changes the context of a calculation.

  • The problem: "I want to see total sales, but only for the 'High-End' product category."
  • The fix: 
High End Sales = CALCULATE(SUM(Sales[Amount]), Products[Category] = "High-End")
Enter fullscreen mode Exit fullscreen mode
  • Why it matters: It lets you ask questions like "what if?" without changing your original data.

2. SUMX (and the other X functions)

This is a row-by-row calculator. It’s more accurate than a simple sum.

  • The problem: "I need total revenue, but I don't have a 'Revenue' column. I have 'Price' and 'Quantity' in 2 different columns."
  • The fix: 
Total Revenue = SUMX(Sales, Sales[Price] * Sales[Quantity])
Enter fullscreen mode Exit fullscreen mode
  • Why it matters: It goes row by row, multiplies the price and quantity for each sale, and then adds them up. It handles complex logic that a simple sum cannot.

3. DATESINPERIOD

Time intelligence is what makes BI powerful. This function looks backwards or forwards in time.

  • The problem: "I want to automatically see the last 3 months of sales, rolling with today’s date."
  • The fix: 
Sales Last 3 Months = 
CALCULATE(
    SUM(Sales[Amount]),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
)
Enter fullscreen mode Exit fullscreen mode
  • Why it matters: Instead of manually filtering dates every month, this automates your reports to always show the most recent, relevant period.

4. FILTER

Sometimes you need to remove data based on a condition that isn’t a simple column value.

  • The problem: "I want to count customers who spent more than $1,000 last year, not just all customers."
  • The fix: 
High Value Customers = 
CALCULATE(
    DISTINCTCOUNT(Customers[ID]),
    FILTER(
        Customers,
        CALCULATE(SUM(Sales[Amount])) > 1000
    )
)
Enter fullscreen mode Exit fullscreen mode
  • Why it matters: It allows you to filter based on the result of a calculation, not just raw data.

5. DIVIDE

A safe way to do division. It prevents the ugly "infinity" or "NaN" errors.

  • The problem: "I need to calculate profit margin, but sometimes the revenue is zero, which breaks the calculation."
  • The fix: 
Profit Margin = DIVIDE([Total Profit], [Total Revenue], 0)
Enter fullscreen mode Exit fullscreen mode
  • Why it matters: The last part (the 0) tells the formula, "If the revenue is zero, just show zero instead of breaking."

Putting it into a real example:

Imagine you are a retail manager.

You want to know: "Which products have a profit margin above 20% and have sold more than 500 units in the last 90 days?"

Using the 5 functions above:

  1. DATESINPERIOD finds the last 90 days.
  2. SUMX calculates the total units sold.
  3. DIVIDE calculates the profit margin.
  4. FILTER looks for products where both conditions (units >500, margin >20%) are true.
  5. CALCULATE brings all that context together to give you the final list.

You don’t need to be a programmer to be good at business analytics.

You just need to know how to ask the right questions and master these 5 building blocks.

Top comments (0)