Power BI DAX Complete Formula List

Row context vs filter context, CALCULATE, time intelligence, REMOVEFILTERS aur business measures tak — har DAX concept ka syntax, example aur behaviour. Common traps aur interview one-liners ke saath. Power BI DAX complete cheat sheet.

📑 Is Article Mein Kya Hai:

• 🧠 PART 0 — DAX ka Core Concept (yeh samjho, sab aasan)
• 🧱 PART 1 — Base Aggregation Measures
• ➗ PART 2 — DIVIDE (sabse zaroori function)
• 🎯 PART 3 — CALCULATE (DAX ka dil)
• 📅 PART 4 — Time Intelligence (Interview ka favourite)
• 🔓 PART 5 — Filter Modifiers (ALL family)
• 🔁 PART 6 — Iterators (X-functions)
• 🧲 PART 7 — Context Functions (advanced but impressive)
• 🎨 PART 8 — Conditional Formatting / Dynamic Measures
• 🚚 PART 9 — Business Measures (Supply Chain)
• 🏗️ PART 10 — Calculated Column vs Measure
• 🧮 PART 11 — Useful DAX Functions Quick List
• 🚫 PART 12 — Common DAX Mistakes (tumhare bhi the)
• 🎤 PART 13 — Interview One-Liners (DAX)
• ✅ Revision Priority
• 🏗️ Bonus — Star Schema Template

DAX me sirf 2 context hote hain. 90% confusion yahin se aati hai.

CALCULATE ka kaam = Row Context ko Filter Context me convert karna. Isko Context Transition kehte hain. Yeh ek line interview me bol do — banda impress ho jayega.

-- Yeh measure hai (filter context me chalta hai) ✅
Total Sales = SUM(Sales[Sales_Amount])

-- Yeh calculated column me galat kaam karega ❌
-- kyunki calculated column me filter context hota hi nahi

-- Basic ⭐
Total Sales      = SUM(Sales[Sales_Amount])
Total Orders     = COUNT(Sales[Order_ID])              -- NULL skip karta hai
Total Rows       = COUNTROWS(Sales)                    -- saari rows ⭐
Distinct Orders  = DISTINCTCOUNT(Sales[Order_ID])      -- unique count ⭐
Blank Rows       = COUNTBLANK(Sales[Discount])
Average Price    = AVERAGE(Sales[Unit_Price])
Max Sale         = MAX(Sales[Sales_Amount])
Min Sale         = MIN(Sales[Sales_Amount])
Total Cost       = SUM(Sales[Unit_Cost])
Total Profit     = [Total Sales] - [Total Cost]        -- measure ko [ ] me reference karo ⭐

⚠️ AVERAGE ka classic trap

-- ❌ GALAT: pehle row-level average, phir unka average
Wrong Avg = AVERAGE(Sales[Sales_Amount])

-- ✅ SAHI: aggregate karo, phir divide karo
Avg Order Value = DIVIDE([Total Sales], [Total Orders])

-- ❌ KABHI aisa mat likho
Margin % = ([Total Sales] - [Total Cost]) / [Total Sales]     -- divide by zero = ERROR

-- ✅ SAHI ⭐
Margin % = DIVIDE([Total Sales] - [Total Cost], [Total Sales], 0)

Syntax: DIVIDE(numerator, denominator, alternateResult)

• Teesra argument = alternate result (divide-by-zero pe yeh return hoga)
• Isko likhna hi chahiye — warna BLANK ya Infinity aa jayega

Golden Rule: * 100 mat karo ⭐

-- ❌ GALAT
OTD % = DIVIDE([On Time], [Total], 0) * 100

-- ✅ SAHI
OTD % = DIVIDE([On Time], [Total], 0)

Measure ki value 0.625 rehne do, aur Power BI me Format → Percentage kar do. Display 62.50% dikhega. Isse conditional formatting aur gauges sahi kaam karte hain.

-- Syntax: CALCULATE(<expression>, <filter1>, <filter2>, ...)

-- Simple filter ⭐
On Time Shipments = CALCULATE([Total Shipments], Shipments[Delivery_Status] = "On-Time")

-- Multiple conditions (AND)
Big COD Orders = CALCULATE([Total Orders],
                   Orders[Payment_Mode] = "COD",
                   Orders[Order_Amount] > 10000)

-- OR condition
Priority Orders = CALCULATE([Total Orders],
                    Orders[Priority] = "High" || Orders[Priority] = "Urgent")

-- FILTER() function se (complex condition ke liye) ⭐
High Value Orders = CALCULATE([Total Orders],
                      FILTER(Orders, Orders[Order_Amount] > 10000 && Orders[Units] > 5))

-- Boolean vs FILTER — performance ⭐
-- Simple comparison → Boolean expression use karo (VertiPaq optimize karta hai)
-- Row-by-row complex logic → FILTER() use karo (Formula Engine, slow)

🏆 The Classic OTD % (interview standard answer)

Total Shipments   = COUNTROWS(Shipments)

On Time Shipments = CALCULATE([Total Shipments], Shipments[Delivery_Status] = "On-Time")

OTD % = DIVIDE([On Time Shipments], [Total Shipments], 0)

Single-measure VAR version (yeh zyada professional lagta hai):

OTD % =
VAR TotalCount  = COUNTROWS(Shipments)
VAR OnTimeCount = CALCULATE(COUNTROWS(Shipments), Shipments[Delivery_Status] = "On-Time")
RETURN
    DIVIDE(OnTimeCount, TotalCount, 0)

⚠️ Pehle: Date Table BANAO (yeh mandatory hai)

DimDate = CALENDARAUTO()          -- data ke hisaab se auto range

-- Ya custom
DimDate = CALENDAR(DATE(2023,1,1), DATE(2026,12,31))

-- Columns add karo
DimDate =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year",       YEAR([Date]),
    "Quarter",     "Q" & FORMAT([Date], "Q"),
    "Month No",    MONTH([Date]),
    "Month Name",  FORMAT([Date], "MMM"),
    "Month Year",  FORMAT([Date], "MMM-YYYY"),
    "Day Name",    FORMAT([Date], "DDDD"),
    "Is Weekday",  WEEKDAY([Date], 2) <= 5
)

Phir: Model view → DimDate[Date] pe right-click → "Mark as Date Table" ✅

4.1 Previous Period ⭐

Previous Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH(DimDate[Date]))
Previous Quarter     = CALCULATE([Total Sales], PREVIOUSQUARTER(DimDate[Date]))
Previous Year Sales  = CALCULATE([Total Sales], PREVIOUSYEAR(DimDate[Date]))
Last Year Same Period= CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))

4.2 DATEADD — custom offset ⭐

Sales 1 Month Ago = CALCULATE([Total Sales], DATEADD(DimDate[Date], -1, MONTH))
Sales 3 Month Ago = CALCULATE([Total Sales], DATEADD(DimDate[Date], -3, MONTH))
Sales Last Year   = CALCULATE([Total Sales], DATEADD(DimDate[Date], -1, YEAR))
Next Month Sales  = CALCULATE([Total Sales], DATEADD(DimDate[Date],  1, MONTH))

🔥 PREVIOUSMONTH vs DATEADD vs PARALLELPERIOD (doc-verified)

SQLBI ke mutabiq: PREVIOUSMONTH(X) internally = PARALLELPERIOD(FIRSTDATE(X), -1, MONTH)

4.3 Cumulative Periods ⭐

MTD Sales = TOTALMTD([Total Sales], DimDate[Date])
QTD Sales = TOTALQTD([Total Sales], DimDate[Date])
YTD Sales = TOTALYTD([Total Sales], DimDate[Date])

-- Custom year end (Indian FY = 31 March) ⭐
FY YTD Sales = TOTALYTD([Total Sales], DimDate[Date], "3/31")

4.4 Growth Measures ⭐

-- Month-over-Month
MoM Sales Growth % =
VAR CurrentSales = [Total Sales]
VAR PrevSales    = [Previous Month Sales]
RETURN
    DIVIDE(CurrentSales - PrevSales, PrevSales, 0)

-- Year-over-Year
LY Sales   = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))

YoY Growth % =
VAR Curr = [Total Sales]
VAR LY   = [LY Sales]
RETURN
    DIVIDE(Curr - LY, LY, 0)

-- Absolute difference
MoM Sales Variance = [Total Sales] - [Previous Month Sales]

4.5 Rolling / Moving Average ⭐

Rolling 3 Month Sales =
CALCULATE(
    [Total Sales],
    DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -3, MONTH)
)

Rolling 7 Day Sales =
CALCULATE(
    [Total Sales],
    DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -7, DAY)
)

Rolling 3M Avg = DIVIDE([Rolling 3 Month Sales], 3)

4.6 Other Date Helpers

First Sale Date  = FIRSTDATE(DimDate[Date])
Last Sale Date   = LASTDATE(DimDate[Date])
Days in Month    = COUNTROWS(DATESMTD(DimDate[Date]))
Working Days     = COUNTROWS(FILTER(DATESBETWEEN(...), WEEKDAY([Date],2) <= 5))
Same Period Next = CALCULATE([Total Sales], NEXTMONTH(DimDate[Date]))

-- ALL: saare filters hata do ⭐
Sales All Products = CALCULATE([Total Sales], ALL(Product))
Sales All Regions  = CALCULATE([Total Sales], ALL(Geography[Region]))

-- ALLEXCEPT: yeh column chhod ke baaki sab hata do
Sales per Product  = CALCULATE([Total Sales], ALLEXCEPT(Sales, Product[Product_Name]))

-- ALLSELECTED: visual ke filters hatao, slicer ke rakho ⭐
Sales All Selected = CALCULATE([Total Sales], ALLSELECTED(Product[Category]))

-- REMOVEFILTERS: Microsoft ka recommended (intent clear hota hai) ⭐
Sales Clear Filter = CALCULATE([Total Sales], REMOVEFILTERS(Product))

-- KEEPFILTERS: existing filter ko override hone se bachao
West Sales = CALCULATE([Total Sales], KEEPFILTERS(Geography[Region] = "West"))

🏆 % of Total (sabse common dashboard measure)

-- % of grand total
% of Total Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Product)), 0)

-- % within slicer selection (visual total ke against)
% of Selected = DIVIDE([Total Sales], CALCULATE([Total Sales], ALLSELECTED(Product)), 0)

-- % of parent category ⭐ (SQLBI recommended pattern)
% of Category =
DIVIDE(
    [Total Sales],
    CALCULATE([Total Sales], REMOVEFILTERS(Product), VALUES(Product[Category]))
)

Comparison table (doc-verified)

-- SUMX: row-by-row calculate karo, phir sum ⭐
Total Revenue = SUMX(Sales, Sales[Units] * Sales[Unit_Price])

-- Profit per row, phir total
Total Profit = SUMX(Sales, (Sales[Unit_Price] - Sales[Unit_Cost]) * Sales[Units])

-- AVERAGEX
Avg Line Value = AVERAGEX(Sales, Sales[Units] * Sales[Unit_Price])

-- COUNTX / MINX / MAXX
Max Line Value = MAXX(Sales, Sales[Units] * Sales[Unit_Price])

-- FILTER + SUMX
High Value Revenue = SUMX(FILTER(Sales, Sales[Order_Amount] > 10000),
                          Sales[Units] * Sales[Unit_Price])

-- RELATED (dusri table se value laao — row context me) ⭐
Line Total with Cost = SUMX(Sales, Sales[Units] * RELATED(Product[Unit_Cost]))

-- RELATEDTABLE (ulta direction — one-to-many)
Order Count per Customer = COUNTROWS(RELATEDTABLE(Sales))

SUM vs SUMX — kab kya?

VALUES(Product[Category])              -- current context ki distinct values (table)
SELECTEDVALUE(Product[Category])       -- single value, warna BLANK ⭐
HASONEVALUE(Product[Category])         -- True/False
ISFILTERED(Sales[Order_Date])          -- kya filter laga hai?
ISCROSSFILTERED(Product[Product_ID])
DISTINCTCOUNT(Product[Product_ID])

-- Dynamic title banane ke liye ⭐
Dynamic Title =
VAR Sel = SELECTEDVALUE(Product[Category], "All Categories")
RETURN "Sales Report — " & Sel

-- Conditional measure
Sales Display =
IF(HASONEVALUE(Product[Category]), [Total Sales], BLANK())

-- Status flag (conditional formatting ke liye) ⭐
Delivery Status Color =
SWITCH(
    TRUE(),
    [OTD %] >= 0.95, "#00A651",      -- green
    [OTD %] >= 0.85, "#FFA500",      -- orange
    "#E81123"                        -- red
)

-- RAG status text
RAG Status =
SWITCH(
    TRUE(),
    [OTD %] >= 0.95, "🟢 On Track",
    [OTD %] >= 0.85, "🟠 At Risk",
    "🔴 Critical"
)

-- IF version
Stock Alert =
IF([Days of Stock] <= [Lead Time Days], "🔴 Reorder Now",
IF([Days of Stock] <= [Lead Time Days] * 1.5, "🟠 Watch", "🟢 Safe"))

-- Data bars / icons ke liye numeric
Stock Score =
VAR DOS = [Days of Stock]
RETURN
    IF(DOS <= 7, 1, IF(DOS <= 15, 2, 3))

SWITCH(TRUE(), ...) vs nested IF ⭐

• 3+ conditions ho toh SWITCH(TRUE()) use karo — padhne me aasan, performance better
• Nested IF sirf 2 conditions tak

-- On-Time Delivery % ⭐
OTD % = DIVIDE([On Time Shipments], [Total Shipments], 0)

-- Fill Rate
Fill Rate % = DIVIDE(SUM(Shipments[Units_Delivered]), SUM(Shipments[Units_Ordered]), 0)

-- OTIF (On-Time In-Full) — dono condition ek saath ⭐
OTIF % =
DIVIDE(
    CALCULATE([Total Shipments],
              Shipments[On_Time_Flag] = 1,
              Shipments[Complete_Flag] = 1),
    [Total Shipments],
    0
)

-- SLA Breach %
SLA Breach % =
DIVIDE(
    CALCULATE([Total Shipments], Shipments[Actual_Date] > Shipments[Promised_Date]),
    [Total Shipments],
    0
)

-- Days of Stock ⭐
Days of Stock = DIVIDE(SUM(Inventory[Current_Stock]), [Daily Avg Sales])

Daily Avg Sales =
DIVIDE(
    CALCULATE([Total Units Sold],
              DATESINPERIOD(DimDate[Date], LASTDATE(DimDate[Date]), -30, DAY)),
    30
)

-- Reorder Point = (Avg Daily Sales × Lead Time) + Safety Stock
Reorder Point = ([Daily Avg Sales] * [Avg Lead Time]) + [Safety Stock]

-- Safety Stock = Z × σ(demand) × √lead_time
Safety Stock =
VAR Z = 1.65                                    -- 95% service level
VAR Sigma = CALCULATE(STDEV.P(Sales[Units]))
VAR LT = [Avg Lead Time]
RETURN Z * Sigma * SQRT(LT)

-- Inventory Turnover
Inventory Turnover = DIVIDE([Total COGS], [Average Inventory Value])

-- Days of Inventory
DOH = DIVIDE(365, [Inventory Turnover])

-- Return Rate / RTO %
RTO % = DIVIDE([Returned Orders], [Total Orders], 0)

-- Cost per Delivery
Cost per Delivery = DIVIDE([Total Logistics Cost], [Total Shipments])

-- ABC classification (calculated column)
ABC Class =
VAR CumPct = Inventory[Cumulative Revenue %]
RETURN
    IF(CumPct <= 0.80, "A", IF(CumPct <= 0.95, "B", "C"))

💳 Fraud Measures (Mastercard)

High Risk Txns =
CALCULATE([Total Transactions], Transactions[Risk_Flag] = "High Risk")

Fraud Rate % = DIVIDE([High Risk Txns], [Total Transactions], 0)

Off Hour Txns =
CALCULATE([Total Transactions],
          FILTER(Transactions, HOUR(Transactions[Txn_Time]) >= 2 &&
                               HOUR(Transactions[Txn_Time]) <= 4))

-- Card velocity flag (calculated column)
Velocity Flag =
VAR PrevTime = CALCULATE(MAX(Transactions[Txn_Time]),
                         FILTER(Transactions, Transactions[Txn_Time] < EARLIER(Transactions[Txn_Time]) &&
                                              Transactions[Card_Number] = EARLIER(Transactions[Card_Number])))
RETURN IF(DATEDIFF(PrevTime, Transactions[Txn_Time], MINUTE) <= 10, "High Velocity", "Normal")

-- Calculated COLUMN (row context me, model me store hota hai)
Sales[Line Total] = Sales[Units] * Sales[Unit_Price]
Sales[Delivery TAT] = DATEDIFF(Sales[Promised_Date], Sales[Actual_Date], DAY)
Sales[Order Month] = FORMAT(Sales[Order_Date], "MMM-YYYY")

-- MEASURE (filter context me, on-the-fly calculate hota hai)
Total Line Sales = SUM(Sales[Line Total])

Math & Stats

ROUND(x, 2)   ·   ROUNDUP(x, 0)   ·   ROUNDDOWN(x, 0)   ·   INT(x)
ABS(x)        ·   SQRT(x)         ·   POWER(x, 2)       ·   MOD(x, 2)
STDEV.P(col)  ·   STDEV.S(col)    ·   VAR.P(col)        ·   MEDIAN(col)
PERCENTILE.INC(col, 0.75)

Text

CONCATENATE(a, b)   ·   a & " " & b        ·   LEFT(s, 3)   ·   RIGHT(s, 4)
MID(s, 2, 4)        ·   LEN(s)             ·   UPPER(s)     ·   LOWER(s)
TRIM(s)             ·   SUBSTITUTE(s,"a","b")  ·   FORMAT(1234.5, "#,##0.00")
FIND("@", s)        ·   SEARCH("@", s)     ·   VALUE("123") ·   UNICHAR/UNICODE

Date

YEAR(d) · MONTH(d) · DAY(d) · HOUR(t) · MINUTE(t) · SECOND(t)
DATE(2024,1,31) · TIME(14,30,0) · TODAY() · NOW() · UTCNOW()
DATEDIFF(d1, d2, DAY)   -- DAY / MONTH / YEAR / HOUR / MINUTE / SECOND
WEEKDAY(d, 2)           -- 1=Monday ... 7=Sunday
WEEKNUM(d) · EOMONTH(d, 0) · EDATE(d, -1) · DATEADD · DATESBETWEEN

Logical

IF(cond, trueVal, falseVal)
SWITCH(TRUE(), cond1, val1, cond2, val2, default)
IFERROR(expr, fallback)     ·   ISBLANK(x)   ·   COALESCE(a, b, c)
AND(a, b)  ·  OR(a, b)  ·  NOT(a)           -- ya && || !

Table

SUMMARIZE(Table, Col1, Col2, "Name", [Measure])
SUMMARIZECOLUMNS(Col1, Col2, "Total", [Measure])     -- ⭐ best practice
ADDCOLUMNS(Table, "NewCol", expr)
SELECTCOLUMNS(Table, "A", Table[Col])
FILTER(Table, condition)
TOPN(5, Table, [Measure], DESC)
RANKX(ALL(Product[Name]), [Total Sales], , DESC, DENSE)
CROSSJOIN(t1, t2)  ·  UNION(t1, t2)  ·  EXCEPT(t1, t2)
VALUES(col)  ·  DISTINCT(col)  ·  COUNTROWS(t)
GENERATE(t1, t2)  ·  NATURALINNERJOIN(t1, t2)

SUMMARIZECOLUMNS — modern best practice ⭐

-- Report table banane ke liye (calculated table)
Sales Summary =
SUMMARIZECOLUMNS(
    Product[Category],
    DimDate[Month Year],
    "Total Sales",  [Total Sales],
    "Orders",       [Total Orders],
    "OTD %",        [OTD %]
)

RANKX — ranking in DAX ⭐

Sales Rank =
RANKX(ALL(Product[Product_Name]), [Total Sales], , DESC, DENSE)

Top 5 Products =
CALCULATETABLE(
    VALUES(Product[Product_Name]),
    TOPN(5, VALUES(Product[Product_Name]), [Total Sales], DESC)
)

Tier 1 — MUST (yeh 15 aana hi chahiye): SUM · COUNTROWS · DISTINCTCOUNT · DIVIDE(a,b,0) · CALCULATE · AVERAGE trap · PREVIOUSMONTH · DATEADD · TOTALYTD · SAMEPERIODLASTYEAR · MoM/YoY growth · ALL() for % of total · VAR/RETURN · IF · Percentage format rule

Tier 2 — Strong impression: DATESINPERIOD (rolling) · SUMX · RELATED/RELATEDTABLE · ALLSELECTED · SWITCH(TRUE()) · RANKX · SUMMARIZECOLUMNS · SELECTEDVALUE · REMOVEFILTERS+VALUES

Tier 3 — Differentiator: Context transition explain karna · KEEPFILTERS · EARLIER · DATESBETWEEN · Star schema justification · ALLEXCEPT ki limitation · Measure branching · Field parameters

        DimDate  ──┐
                   │
  DimProduct ──►  FACT: Sales  ◄── DimCustomer
                   │
        DimWarehouse ─┘

  Fact table:  numeric measures + foreign keys (date, product, customer, warehouse)
  Dim tables:  descriptive attributes

Interview me yeh bolo: "Main hamesha star schema use karta hoon — ek fact table aur connected dimension tables. Isse DAX simple rehta hai, relationships unambiguous hoti hain, aur VertiPaq engine fastest scan kar pata hai."

Syntax Microsoft Learn + SQLBI documentation se verify kiya gaya hai. DAX sandbox me execute nahi hua.

Power BI DAX Cheat Sheet — Complete!

DAX syntax Microsoft Learn aur SQLBI documentation se verify ki gayi hai. Jahan behaviour subtle hai wahan doc reference diya gaya hai — honest marking, guess nahi.

Happy Learning & Keep Exploring! 🚀