Ravindra BagaleCourses & study guides Track your progress

Excel → Power BI Bridge

Bridge 3. Excel formulas → DAX

By

Friends,

Friends, today we translate Excel formulas into DAX. Same questions. New home. We keep LibreOffice-safe Excel practice in the workbook, and we show the Power BI measure next to it.

About this data: Fictional multi-industry lines — retail (Pune, Manchester), banking desk (Nashik, Dubai), clinic waits (Kolhapur), IT tickets (Hyderabad), logistics (Berlin). Currencies ₹ $ £ € appear on purpose; never sum mixed currencies without a rule. See the About sheet in the workbook.
Download practice .xlsx

Practice workbook and revision cards load from s3://ravindrabagale-cards/excel/bridge/.

What you will learn

  • Rewrite SUMIFS / COUNTIFS thinking as CALCULATE measures.
  • Replace most VLOOKUP pulls with relationships + RELATED.
  • Use IF, SWITCH, and DIVIDE safely.
  • Respect filter context and currency rules.

A short story

Amit keeps six SUMIFS blocks for Nashik. Ananya copies COUNTIFS for Hyderabad tickets. Every new city means another paste. One model with a few measures ends that copy-paste life — after you map the formulas honestly.

Ravindra Bagale’s Tip

Always write the base measure (plain SUM) before CALCULATE variants. Name measures like Total Amount (INR) when currency matters.

1. Formula mindset → measure mindset

Friends,

Friends, a measure is a smart calculator. Filters walk in; the number walks out.

Why: In Excel you often write a formula in a cell next to data. In Power BI you usually write a measure that reacts to filters.

What: Cell formulas vs measures in filter context.

How:

  1. Excel: put =SUM(C:C) or =SUMIFS(...) in a cell or table column.
  2. Power BI: create a measure Total Sales = SUM(Sales[Amount]).
  3. Drop the measure on a visual. Slicers change the answer automatically.
  4. Do not paste the same SUMIFS in 40 cells — one measure serves all visuals.
ExcelPower BI / DAXResultMeaning
Excel cell
=SUMIFS(Amount,City,"Pune")		 DAX measure
Pune Sales = CALCULATE(SUM(Sales[Amount]), Sales[City]="Pune")		 ₹1,25,000 Priya’s Pune kirana total when City slicer = Pune (or hard-filtered).

2. SUMIFS → CALCULATE

Friends,

Base measure first. Then wrap CALCULATE. Copy-paste SUMIFS into 20 cards is the old world.

Why: SUMIFS is the Excel comfort food for “total where…”. CALCULATE is the DAX door for the same idea — and more.

What: Replace multi-condition SUMIFS with CALCULATE + filters (or slicers).

How:

  1. Write the base measure first: Total Amount = SUM(Sales[Amount]).
  2. Add conditions with CALCULATE(Total Amount, Sales[City]="Nashik", Sales[Status]="Delivered").
  3. Prefer letting slicers filter the base measure when the report is interactive.
  4. Remember: CALCULATE can also remove filters (ALL) — power that SUMIFS does not give easily.
ExcelPower BI / DAXResultMeaning
Excel
=SUMIFS(Amount,City,"Nashik",Status,"Delivered")		 DAX
CALCULATE([Total Amount], Sales[City]="Nashik", Sales[Status]="Delivered")		 ₹42,300 Amit’s Nashik branch delivered lines only.

3. COUNTIF / COUNTIFS → COUNTROWS + FILTER / CALCULATE

Friends,

COUNTBLANK friends: use CALCULATE with BLANK checks carefully — blanks are not always zero.

Why: Counting rows under conditions is daily work in retail and IT tickets.

What: COUNTROWS of a filtered table, or CALCULATE(COUNTROWS(...), ...).

How:

  1. Excel: =COUNTIFS(Status,"Open",City,"Hyderabad").
  2. DAX option A: CALCULATE(COUNTROWS(Tickets), Tickets[Status]="Open", Tickets[City]="Hyderabad").
  3. DAX option B: COUNTROWS(FILTER(Tickets, Tickets[Status]="Open" && Tickets[City]="Hyderabad")).
  4. Prefer CALCULATE style for measures that must respect slicers cleanly.
ExcelPower BI / DAXResultMeaning
Excel
=COUNTIFS(Status,"Open",City,"Hyderabad")		 DAX
CALCULATE(COUNTROWS(Tickets), Tickets[Status]="Open", Tickets[City]="Hyderabad")		 17 Ananya’s open IT tickets in Hyderabad.

4. VLOOKUP / XLOOKUP → relationships + RELATED

Friends,

If you still VLOOKUP inside Power BI like Excel, pause. Draw the relationship line first.

Why: Looking up a name from an ID is the classic second-sheet problem. Models replace most lookups.

What: Relate tables on the key; pull the column with RELATED in a calculated column, or just use the field in visuals.

How:

  1. Do not load a wide flat table with ProductName pasted on every fact row if you can help it.
  2. Load Sales and Product. Model view: Sales[ProductID] many-to-one Product[ProductID].
  3. In a calculated column on Sales: Product Name = RELATED(Product[ProductName]).
  4. Often you need no column — put Product[ProductName] on the visual axis directly.
  5. LOOKUPVALUE exists for odd cases; relationships are the default habit.
ExcelPower BI / DAXResultMeaning
Excel
=VLOOKUP(A2,Product!A:B,2,FALSE)		 DAX
RELATED(Product[ProductName]) (after relationship)		 Atta 5kg Sofia’s Berlin lane line gets the product label from Product table.

5. IF → IF / SWITCH

Friends,

Nested IF over three levels? Try SWITCH. Your future self will thank you.

Why: Branching logic moves almost one-to-one, with SWITCH for clean multi-label maps.

What: IF for two-way tests; SWITCH for status → label maps.

How:

  1. Excel: =IF(B2>=1000,"Big","Small").
  2. DAX measure/column: IF(Sales[Amount]>=1000, "Big", "Small").
  3. Excel nested IF for status colours → DAX SWITCH(Tickets[Status], "Open","Needs work", "Closed","Done", "Other").
  4. Keep labels short for visuals and cards.
ExcelPower BI / DAXResultMeaning
Excel
=IF(Amount>=1000,"Big","Small")		 DAX
IF(Sales[Amount]>=1000, "Big", "Small")		 Big Omar’s €1200 Dubai desk voucher flagged Big.

6. AVERAGEIF → AVERAGEX / CALCULATE(AVERAGE)

Friends,

Always name the measure with the grain: Average Wait (mins) — not just Average.

Why: Conditional averages appear in healthcare wait times and delivery fees.

What: CALCULATE(AVERAGE(...), filters) or AVERAGEX over a filtered table.

How:

  1. Excel: =AVERAGEIF(City,"Kolhapur",WaitMins).
  2. DAX: CALCULATE(AVERAGE(Visits[WaitMins]), Visits[City]="Kolhapur").
  3. For “average of a row-by-row expression”, learn AVERAGEX later.
  4. Watch blanks: AVERAGE in DAX skips blanks like Excel usually does.
ExcelPower BI / DAXResultMeaning
Excel
=AVERAGEIF(City,"Kolhapur",WaitMins)		 DAX
CALCULATE(AVERAGE(Visits[WaitMins]), Visits[City]="Kolhapur")		 18 mins Neha’s Kolhapur clinic average wait.

7. Safe division (IF / IFERROR → DIVIDE)

Friends,

Blank often charts cleaner than zero. Ask what the manager should see.

Why: Division by zero breaks dashboards. Excel uses IF or IFERROR; DAX has DIVIDE.

What: DIVIDE(numerator, denominator, alternate_result).

How:

  1. Excel habit: =IF(B2=0,0,A2/B2) or IFERROR(A2/B2,0).
  2. DAX: DIVIDE(SUM(Sales[Amount]), SUM(Sales[Qty]), 0).
  3. Prefer DIVIDE over manual IF for readability.
  4. Decide if alternate should be BLANK() (better for charts) or 0.
ExcelPower BI / DAXResultMeaning
Excel
=IF(Qty=0,0,Amount/Qty)		 DAX
DIVIDE(SUM(Sales[Amount]), SUM(Sales[Qty]), 0)		 ₹250 Unit price style metric for Pune lines with qty > 0.

8. Text joins and case (LibreOffice-safe habits)

Friends,

Clean early in Power Query. DAX should calculate, not mop floors.

Why: You still clean text. Power Query does most cleaning; DAX has UPPER/LOWER/LEFT too.

What: Prefer Power Query for cleaning; use DAX text functions sparingly on measures/columns.

How:

  1. Excel class-safe join: =A2 & " - " & B2 (avoid TEXTJOIN in LibreOffice checks).
  2. DAX: Sales[City] & " - " & Sales[Status] in a calculated column if needed.
  3. Case: UPPER / LOWER / PROPER-style cleaning is happier in Power Query.
  4. Do not build giant text report columns in DAX when a visual label will do.
ExcelPower BI / DAXResultMeaning
Excel
=A2 & " - " & B2		 DAX
Sales[City] & " - " & Sales[Status]		 Pune - Delivered Label for a detail table — still prefer PQ for heavy cleans.

9. Why the same measure changes on a visual

Friends,

About this data must say which currency a total uses. Never sum ₹ and € as one number.

Why: Students panic when Total Sales shows different numbers in two cards. That is filter context, not a bug.

What: Slicers, rows of a matrix, and page filters all shape CALCULATE.

How:

  1. Put [Total Amount] on a card — sees all data (unless page filters).
  2. Put the same measure on a matrix with City on rows — each row filters City.
  3. Excel parallel: a PivotTable value changes per row label.
  4. CALCULATE adds or replaces filters on top of that context.
ExcelPower BI / DAXResultMeaning
Excel Pivot
Sum of Amount by City		 PBI Matrix
Same measure [Total Amount] + City on rows		 Per city Manchester £ and Pune ₹ rows can sit in one model with a currency column — do not blindly sum mixed currency.

10. Pocket cheat sheet (print this card later)

Friends,

Master five. Then open CALCULATE with ALL / time intelligence in the Power BI DAX chapters.

Why: A short map beats a long book on day one.

What: Highest-traffic Excel → DAX replacements.

How:

  1. SUM / SUMIF(S) → SUM + CALCULATE
  2. COUNT / COUNTIF(S) → COUNTROWS + CALCULATE
  3. AVERAGEIF → CALCULATE(AVERAGE(...), ...)
  4. IF → IF; nested status maps → SWITCH
  5. VLOOKUP/XLOOKUP → relationship + RELATED (or visual fields)
  6. A/B with zero risk → DIVIDE
  7. Helper columns forever → ask if a measure is enough
ExcelPower BI / DAXResultMeaning
Excel comfort
SUMIFS + VLOOKUP + IF		 DAX starter kit
SUM, CALCULATE, RELATED, IF, DIVIDE		 Enough Chen Wei’s Singapore demo pack runs on these five.

Practice tasks (12)

Use bridge-dax-practice.xlsx. Excel side must run in LibreOffice: avoid XLOOKUP, TEXTJOIN, TEXTSPLIT. DAX column is written as text for Desktop practice.

  1. On Sales sheet, write Excel Total Amount with SUM (LibreOffice-safe).
    Hint

    Simple =SUM(Amount column).

  2. Write Excel SUMIFS for City=Pune.
    Hint

    =SUMIFS(Amount,City,"Pune")

  3. Write Excel SUMIFS for City=Nashik and Status=Delivered.
    Hint

    Two criteria SUMIFS.

  4. Write COUNTIFS for Status=Open and City=Hyderabad on Tickets.
    Hint

    Open + Hyderabad.

  5. Simulate a lookup: VLOOKUP ProductID into ProductName (range lookup FALSE).
    Hint

    Product sheet columns A:B.

  6. Add IF flag Big/Small for Amount>=1000.
    Hint

    =IF(Amount>=1000,"Big","Small")

  7. Use IF to protect division Amount/Qty when Qty=0.
    Hint

    =IF(Qty=0,0,Amount/Qty)

  8. On DAX_Translate, fill the DAX column for each Excel formula (words + code).
    Hint

    Use chapter examples.

  9. Explain in one sentence why RELATED needs a relationship.
    Hint

    Write on Notes sheet.

  10. Mark which rows are mixed-currency risks before any total.
    Hint

    Currency column — ₹ vs €.

  11. AVERAGEIF wait mins for Kolhapur on Visits.
    Hint

    =AVERAGEIF(City,"Kolhapur",WaitMins)

  12. Rewrite nested IF status labels as a SWITCH plan on paper (Open/Closed/Other).
    Hint

    Three-line SWITCH sketch.

Quick quiz

Q1. Best first DAX habit for totals?

Q2. SUMIFS maps most closely to…

Q3. Preferred replacement for most VLOOKUP name pulls?

Q4. DIVIDE is mainly for…

Q5. SWITCH is happier than deep nested IF when…

Q6. Same measure, different number on two visuals — often because of…

Q7. COUNTIFS open tickets in one city becomes…

Q8. Before summing Amount across Pune and Berlin rows you must…

Loading practice tasks...

  1. Rows in the fact table (Easy): How many orders are in the Sales table?
  2. Total amount (Easy): Add up the Amount column of Sales.
  3. Total quantity (Easy): Add up the Qty column of Sales.
  4. Products in the dimension (Easy): How many products are in the Products table?
  5. Products actually sold (Medium): How many of the 5 Product IDs (P01 to P05) appear at least once in Sales?
  6. Amount for one city (Medium): Add up Amount where City is "London".
  7. Product name through the relationship (Medium): What is the Product name for Product ID "P03"?
  8. Sales of one product (Medium): Add up Amount where Product ID is "P03".
  9. Average order amount (Medium): Find the average Amount, rounded to 0 decimals.
  10. Category of the first order (Hard): Find the Category of the product in the first order.
  11. Biggest order in London (Hard): What is the largest Amount where City is "London"?
  12. Share of P03 (Hard): What share of the total Amount comes from Product ID "P03"? Round to 3 decimals.

See all practice tasks in the Task Hub →

Quick revision cards (3)

Approved Excel card style: standalone, no tip/photo section, QR to this lesson.

Revision card: SUMIFS to CALCULATE
Card 1 — SUMIFS → CALCULATE
Revision card: VLOOKUP to RELATED
Card 2 — VLOOKUP → RELATED
Revision card: IF SWITCH DIVIDE
Card 3 — IF / SWITCH / DIVIDE