Top 10 ChatGPT & DeepSeek Prompts to Write Complex DAX Measures in Power BI
Top 10 ChatGPT & DeepSeek Prompts to Write Complex DAX Measures in Power BI 🤖
AI se DAX likhwana ek art hai — sahi prompt doge toh production-ready DAX milega, galat doge toh garbage. Yeh 10 battle-tested prompts copy karo aur complex DAX measures minutes mein banao. ChatGPT aur DeepSeek dono ke liye optimized. Data Insights par.
📑 Why This Matters:
2026 mein har Data Analyst AI tools (ChatGPT, DeepSeek, Copilot) use kar raha hai DAX likhne ke liye. But problem yeh hai — generic prompt = generic (galat) DAX. Agar tum sahi prompt structure follow karo — AI bahut powerful DAX likh sakta hai jo directly production mein use ho sake. Neeche 10 best prompts hain with exact templates aur real DAX outputs.
🎯 Perfect DAX Prompt Ka Structure
Har prompt mein yeh 5 elements hone chahiye — isse AI ko exact context milta hai:
| # | Element | Kya Likho | Example |
|---|---|---|---|
| 1 | Role | AI ko batao woh kya hai | "You are a Power BI DAX expert" |
| 2 | Data Model | Tables, columns, relationships batao | "Sales[Amount], Calendar[Date], Products[Category]" |
| 3 | Requirement | Exact calculation clearly describe karo | "Calculate YoY growth % for each month" |
| 4 | Edge Cases | Special situations mention karo | "Handle BLANK previous year, divide by zero" |
| 5 | Output Format | DAX kaise chahiye | "Use VAR/RETURN pattern, add comments" |
✅ Good Prompt Example: "You are a Power BI DAX expert. I have Sales[Amount], Calendar[Date] (marked as date table). Write a YoY Growth % measure using SAMEPERIODLASTYEAR. Use VAR/RETURN pattern. Handle BLANK previous year with IF(ISBLANK). Format as percentage."
Prompt 1: Year-over-Year (YoY) Growth %
🎯 Use Case: Monthly/Quarterly YoY growth comparison — har dashboard mein chahiye.
📋 The Prompt (Copy-Paste Ready):
You are a Power BI DAX expert.
My data model:
Fact table: Sales (columns: OrderID, OrderDate, Amount, CustomerID, ProductID)
Date table: Calendar (columns: Date, Year, Quarter, MonthName, MonthNum)
Relationship: Calendar[Date] → Sales[OrderDate] (1:N, active)
Calendar table is marked as Date Table
Write a DAX measure for:
"YoY Growth %" — Calculate Year-over-Year growth percentage comparing current period with same period last year.
Requirements:
Use SAMEPERIODLASTYEAR for previous year
Use VAR/RETURN pattern for readability
Handle edge cases: BLANK previous year (return BLANK, not error)
Use DIVIDE for safe division (no divide by zero)
Add comments explaining each step
Format-ready for percentage display🤖 AI Output (What You'll Get):
// YoY Growth % — Year-over-Year comparison YoY Growth % = VAR CurrentSales = SUM(Sales[Amount])
VAR PreviousYearSales =
CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Calendar[Date])
)
VAR GrowthAmount =
CurrentSales - PreviousYearSales
RETURN
// Safe division + BLANK handling
IF(
ISBLANK(PreviousYearSales),
BLANK(),
DIVIDE(GrowthAmount, PreviousYearSales, 0)
)📋 Why This Prompt Works: Table names, column names, relationship, date table marking — sab specifically mention kiya. Edge cases (BLANK, division) bataye. Output format (VAR/RETURN, comments) specify kiya. AI ko full context mila — production-ready DAX mila!
Prompt 2: Running Total / Cumulative Sum
🎯 Use Case: Line chart mein growth trajectory dikhana — YTD target tracking.
📋 The Prompt (Copy-Paste Ready):
You are a Power BI DAX expert.
My data model:
Sales table: Sales[Amount], Sales[OrderDate]
Calendar table: Calendar[Date] (marked as Date Table)
Relationship: Calendar[Date] → Sales[OrderDate] (1:N)
Write TWO DAX measures:
"Running Total" — Cumulative sum that never resets (beginning to current date)
"YTD Running Total" — Cumulative sum that resets every January 1st
Requirements:
Use CALCULATE + FILTER + ALL pattern for running total
Use TOTALYTD for YTD version
Use VAR/RETURN pattern
Add comments explaining the difference
Both should work in line charts with monthly axis🤖 AI Output:
// 1. Running Total — NEVER resets (all-time cumulative) Running Total = VAR CurrentDate = MAX(Calendar[Date])
RETURN
CALCULATE(
SUM(Sales[Amount]),
FILTER(
ALL(Calendar),
Calendar[Date]
// 2. YTD Running Total — Resets every Jan 1st
YTD Sales =
TOTALYTD(
SUM(Sales[Amount]),
Calendar[Date]
)Prompt 3: Dynamic Ranking with Slicer Support
🎯 Use Case: Dashboard mein products/customers ko dynamically rank karna — slicer change karo toh ranking update ho.
📋 The Prompt:
You are a Power BI DAX expert.
My data model:
Sales[Amount], Products[ProductName], Products[Category]
Relationship: Products → Sales (1:N)
Write a DAX measure:
"Product Rank" — Rank products by total sales within current slicer selection.
Requirements:
Use RANKX with ALLSELECTED (dynamic — respects slicers)
Use Dense ranking (no gaps)
Show BLANK
on Total row (use HASONEVALUE check)
Skip products with zero/blank sales
Descending
order (highest sales = rank 1)
Add comments🤖 AI Output:
// Dynamic Product Ranking — respects slicer selections Product Rank = VAR CurrentSales = SUM(Sales[Amount])
RETURN
// Only show rank for individual products, not totals
IF(
HASONEVALUE(Products[ProductName])
&& NOT(ISBLANK(CurrentSales))
&& CurrentSales > 0,
RANKX(
// Rank within user's slicer selection
FILTER(
ALLSELECTED(Products[ProductName]),
CALCULATE(SUM(Sales[Amount])) > 0
),
CALCULATE(SUM(Sales[Amount])),
,
DESC,
Dense
),
BLANK()
)Prompt 4: Rolling Average (3-Month / 12-Month)
🎯 Use Case: Trend smoothing — seasonal fluctuations remove karke long-term trend dikhana.
📋 The Prompt:
You are a Power BI DAX expert.
My data model:
Sales[Amount], Sales[OrderDate]
Calendar[Date] (Date Table, marked)
Relationship: Calendar[Date] → Sales[OrderDate] (1:N)
Write a DAX measure:
"3-Month Rolling Average" — Moving average of last 3 months sales.
Requirements:
Use DATESINPERIOD function for date window
Use VAR/RETURN pattern
Divide by 3 (or use dynamic month count for incomplete periods)
Use DIVIDE for safe division
Should work correctly in line charts with monthly axis
Add a variant: "12-Month Rolling Average" as well🤖 AI Output:
// 3-Month Rolling Average Rolling Avg 3M = VAR LastDate = MAX(Calendar[Date])
VAR SalesLast3M =
CALCULATE(
SUM(Sales[Amount]),
DATESINPERIOD(
Calendar[Date],
LastDate,
-3,
MONTH
)
)
RETURN
DIVIDE(SalesLast3M, 3, 0)
// 12-Month Rolling Average (for long-term trend)
Rolling Avg 12M =
VAR LastDate =
MAX(Calendar[Date])
VAR SalesLast12M =
CALCULATE(
SUM(Sales[Amount]),
DATESINPERIOD(
Calendar[Date],
LastDate,
-12,
MONTH
)
)
RETURN
DIVIDE(SalesLast12M, 12, 0)Prompt 5: Percentage of Total (3 Variations)
🎯 Use Case: Pie charts, bar charts mein contribution % dikhana — category, region, product level par.
📋 The Prompt:
You are a Power BI DAX expert.
My data model:
Sales[Amount], Products[ProductName], Products[Category]
Customers[Region]
Standard star schema relationships
Write THREE DAX measures for percentage of total:
"% of Grand Total" — Each item's % of EVERYTHING (ignores all slicers)
"% Within Category" — Each product's % within its category
"% of Selection" — Each item's % of what user has selected in slicers
Requirements:
Use ALL for grand total
Use ALLEXCEPT for within category
Use ALLSELECTED for user selection
Use DIVIDE for safe division
Use VAR/RETURN pattern
Add comments explaining which function does what
🤖 AI Output:
// 1. % of Grand Total — ignores ALL filters % of Grand Total = VAR CurrentSales = SUM(Sales[Amount]) VAR GrandTotal = CALCULATE(SUM(Sales[Amount]), ALL(Sales)) RETURN DIVIDE(CurrentSales, GrandTotal, 0)
// 2. % Within Category — keeps category filter, removes product
% Within Category =
VAR CurrentSales = SUM(Sales[Amount])
VAR CategoryTotal =
CALCULATE(
SUM(Sales[Amount]),
ALLEXCEPT(Products, Products[Category])
)
RETURN
DIVIDE(CurrentSales, CategoryTotal, 0)
// 3. % of Selection — respects user's slicer choices
% of Selection =
VAR CurrentSales = SUM(Sales[Amount])
VAR SelectedTotal =
CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Sales))
RETURN
DIVIDE(CurrentSales, SelectedTotal, 0)Prompt 6: KPI Card with Arrow Indicator
🎯 Use Case: Executive dashboard mein "₹5L (+25% ▲)" jaisa KPI card dikhana.
📋 The Prompt:
You are a Power BI DAX expert.
Write a DAX measure that creates a KPI text display showing:
Current month sales amount (formatted in Lakhs/Crores)
MoM growth percentage
Arrow indicator (▲ for growth, ▼ for decline, — for zero)
Color context: green for positive, red for negative
Example output: "₹5.2L (▲ +12.5%)" or "₹3.8L (▼ -8.2%)" or "₹4.0L (— 0.0%)"
My model:
Sales[Amount], Sales[OrderDate]
Calendar[Date] (Date Table)
Requirements:
Use DATEADD for previous month
Handle BLANK previous month (show "N/A")
Use FORMAT for Indian number formatting
Use SWITCH(TRUE()) for conditional logic
VAR/RETURN pattern with comments🤖 AI Output:
// KPI Card — Sales with MoM indicator KPI Display = VAR CurrentSales = SUM(Sales[Amount]) VAR PreviousMonth = CALCULATE( SUM(Sales[Amount]), DATEADD(Calendar[Date], -1, MONTH) ) VAR Growth = DIVIDE(CurrentSales - PreviousMonth, PreviousMonth, 0) VAR SalesText = IF( CurrentSales >= 10000000, "₹" & FORMAT(CurrentSales / 10000000, "0.0") & "Cr", "₹" & FORMAT(CurrentSales / 100000, "0.0") & "L" ) VAR GrowthText = SWITCH( TRUE(), ISBLANK(PreviousMonth), "N/A", Growth > 0, "▲ +" & FORMAT(Growth, "0.0%"), Growth < 0, "▼ " & FORMAT(Growth, "0.0%"), "— 0.0%" ) RETURN SalesText & " (" & GrowthText & ")"Prompt 7: Customer Segmentation (RFM-Style)
🎯 Use Case: Customers ko categories mein divide karna — Power User, Regular, Casual, Inactive.
📋 The Prompt:
You are a Power BI DAX expert specializing in customer analytics.
My data model:
Sales[Amount], Sales[OrderDate], Sales[CustomerID]
Customers[CustomerID], Customers[CustomerName]
Calendar[Date] (Date Table)
Relationships: Customers → Sales (1:N), Calendar → Sales (1:N)
Write a CALCULATED COLUMN in Customers table:
"Customer Segment" — Categorize customers based on total order count:
"Power User" = 10+ orders
"Regular" = 5-9 orders
"Casual" = 2-4 orders
"One-time" = exactly 1 order
"Inactive" = 0 orders
Requirements:
This is a CALCULATED COLUMN (not measure)
Use RELATEDTABLE or CALCULATE + COUNTROWS
Use SWITCH(TRUE()) for categorization
Add comments
🤖 AI Output:
// Calculated Column in Customers table Customer Segment = VAR OrderCount = COUNTROWS(RELATEDTABLE(Sales))
RETURN
SWITCH(
TRUE(),
OrderCount >= 10, "Power User",
OrderCount >= 5, "Regular",
OrderCount >= 2, "Casual",
OrderCount = 1, "One-time",
"Inactive"
)Prompt 8: Conditional Formatting Color Measure
🎯 Use Case: Matrix/Table visuals mein dynamic colors — green for positive, red for negative, yellow for neutral.
📋 The Prompt:
You are a Power BI DAX expert.
I already have a measure "[YoY Growth %]" that returns growth percentage.
Write a DAX measure:
"Growth Color" — Returns HEX color code based on YoY growth value.
Logic:
Growth > 10% → Dark Green (#00B050)
Growth > 0% → Light Green (#92D050)
Growth = 0% → Yellow (#FFC000)
Growth > -10% → Orange (#FF8C00)
Growth ≤ -10% → Red (#FF0000)
BLANK growth → Gray (#808080)
This measure will be used in:
Conditional Formatting → Background Color → Field Value → [Growth Color]
Requirements:
Use SWITCH(TRUE()) for clean conditional
Reference existing [YoY Growth %] measure
Return string (HEX color code)
Handle BLANK/NULL cases
🤖 AI Output:
// Conditional Color based on YoY Growth Growth Color = VAR GrowthPct = [YoY Growth %]
RETURN
SWITCH(
TRUE(),
ISBLANK(GrowthPct), "#808080",
GrowthPct > 0.10, "#00B050",
GrowthPct > 0, "#92D050",
GrowthPct = 0, "#FFC000",
GrowthPct > -0.10, "#FF8C00",
"#FF0000"
)Prompt 9: Dynamic Metric Selector (Slicer-Driven)
🎯 Use Case: Ek chart mein user slicer se choose kare — "Sales", "Quantity", "Profit" — chart dynamically switch ho.
📋 The Prompt:
You are a Power BI DAX expert.
I want to create a dynamic metric selector
where user chooses which metric to display
from a slicer.
Step 1: Create a disconnected MetricTable with
values: "Revenue", "Quantity", "Avg Order Value", "Order Count"
Step 2: Write DAX measure "Selected Metric" that returns the appropriate calculation based
on user's slicer selection.
My data model:
Sales[Amount], Sales[Qty], Sales[OrderID]
Requirements:
Use SELECTEDVALUE to get slicer selection
Use SWITCH for metric routing
Default to Revenue if nothing selected
Handle BLANK safely
VAR/RETURN pattern🤖 AI Output:
// Step 1: Create disconnected table (Enter Data) // MetricTable with column [Metric]: // "Revenue",
"Quantity", "Avg Order Value", "Order Count"
// Step 2: Dynamic Metric Measure
Selected Metric =
VAR SelectedMetric =
SELECTEDVALUE(MetricTable[Metric], "Revenue")
RETURN
SWITCH(
SelectedMetric,
"Revenue",
SUM(Sales[Amount]),
"Quantity",
SUM(Sales[Qty]),
"Avg Order Value",
DIVIDE(
SUM(Sales[Amount]),
DISTINCTCOUNT(Sales[OrderID]),
0
),
"Order Count",
DISTINCTCOUNT(Sales[OrderID]),
SUM(Sales[Amount])
)Prompt 10: Complete Calendar/Date Table Creation
🎯 Use Case: Time Intelligence ka foundation — Date Table ke bina YoY, MoM, YTD sab fail hoga.
📋 The Prompt:
You are a Power BI DAX expert.
Create a complete DAX Calendar Table using ADDCOLUMNS + CALENDAR/CALENDARAUTO.
Date range: 2023-01-01 to 2026-12-31
Include these columns:
Date (primary key)
Year (2023, 2024, ...)
Quarter ("Q1", "Q2", ...)
MonthNum (1-12)
MonthName ("January", "February", ...)
Day (1-31)
Weekday ("Monday", "Tuesday", ...)
WeekdayNum (1=Monday, 7=Sunday)
YearMonth ("2026-01" format for sorting)
Financial Year ("FY 2025-2026" for April-March Indian FY)
IsWeekend (TRUE/FALSE)
Requirements:
Use ADDCOLUMNS + CALENDAR for precise control
Use FORMAT for month/day names
Indian Financial Year: April-March
MonthName should be sortable by MonthNum
Add comments for each column
After creation: Mark as Date Table + Sort MonthName by MonthNum
🤖 AI Output:
// Complete Calendar Table — Modeling tab → New Table Calendar = ADDCOLUMNS( CALENDAR( DATE(2023, 1, 1), DATE(2026, 12, 31) ), // Year "Year", YEAR([Date]),
text
// Quarter (Q1, Q2, Q3, Q4)
"Quarter", "Q" & QUARTER([Date]),
// Month Number (1-12)
"MonthNum", MONTH([Date]),
// Month Name (January, February...)
"MonthName", FORMAT([Date], "MMMM"),
// Day (1-31)
"Day", DAY([Date]),
// Weekday Name
"Weekday", FORMAT([Date], "dddd"),
// Weekday Number (1=Monday, 7=Sunday)
"WeekdayNum", WEEKDAY([Date], 2),
// YearMonth for sorting (2026-01)
"YearMonth", FORMAT([Date], "YYYY-MM"),
// Indian Financial Year (April-March)
"Financial Year",
"FY " &
IF(
MONTH([Date]) >= 4,
YEAR([Date]) & "-" & YEAR([Date]) + 1,
YEAR([Date]) - 1 & "-" & YEAR([Date])
),
// IsWeekend (Saturday=6, Sunday=7)
"IsWeekend",
IF(
WEEKDAY([Date], 2) >= 6,
TRUE(),
FALSE()
)
)
// After creating table:
// 1. Table Tools → Mark as Date Table → Select [Date]
// 2. Data View → Select MonthName → Sort by Column → MonthNum
// 3. Create relationship: Calendar[Date] → Sales[OrderDate]💡 Bonus: 5 Pro Tips for Better AI DAX Prompts
Tip 1: Always Mention Table Names & Column Names
"Sales[Amount]" NOT just "sales amount". AI ko exact syntax chahiye — Table[Column] format mein likho. Warna AI apne assume kar lega — galat table names aayenge.
Tip 2: Specify Relationships
"Calendar[Date] → Sales[OrderDate] (1:N, active)" — AI ko relationship pata hoga toh RELATED, RELATEDTABLE, CALCULATE correctly likhega. Bina relationship bataye AI guess karega — aksar galat.
Tip 3: Request VAR/RETURN Pattern
"Use VAR/RETURN pattern with comments" — bina iske AI ek giant nested formula likhega jo readable nahi hoga. VAR/RETURN se steps clearly dikhte hain, debug karna easy hota hai.
Tip 4: Mention Edge Cases
"Handle BLANK, handle divide by zero, handle no previous year data" — agar nahi batayoge toh AI simple version dega jo production mein crash karega. Edge cases = bulletproof DAX.
Tip 5: Ask "Explain This DAX" to AI
Jab bhi AI se DAX lo — next prompt mein bolo "Explain this DAX measure line by line in simple Hinglish." AI poora walkthrough dega — tum samjhoge kya likha hai, blindly paste nahi karoge. Learning + working dono sath mein!
📋 All 10 Prompts — Quick Reference
| # | Prompt Purpose | Key DAX Functions | Difficulty |
|---|---|---|---|
| 1 | YoY Growth % | SAMEPERIODLASTYEAR, DIVIDE | Medium |
| 2 | Running Total / YTD | CALCULATE, FILTER, ALL, TOTALYTD | Medium |
| 3 | Dynamic Ranking | RANKX, ALLSELECTED, HASONEVALUE | Hard |
| 4 | Rolling Average | DATESINPERIOD, DIVIDE | Medium |
| 5 | % of Total (3 types) | ALL, ALLEXCEPT, ALLSELECTED | Hard |
| 6 | KPI Card Display | DATEADD, SWITCH, FORMAT | Hard |
| 7 | Customer Segmentation | RELATEDTABLE, SWITCH(TRUE()) | Medium |
| 8 | Conditional Colors | SWITCH(TRUE()), HEX codes | Easy |
| 9 | Dynamic Metric Selector | SELECTEDVALUE, SWITCH | Medium |
| 10 | Calendar Table Creation | ADDCOLUMNS, CALENDAR, FORMAT | Medium |
🤖 AI + DAX = Superpowered Data Analyst!
10 battle-tested prompts — copy karo, paste karo ChatGPT ya DeepSeek mein, production-ready DAX lo. Yaad rakho — better prompt = better DAX. Table names, relationships, edge cases, output format — sab clearly batao. AI tumhara DAX assistant hai — use it wisely! Data Insights par Power BI Masterclass (7 Parts), SQL Topic Wise, Python Handbook, aur bahut kuch already available hai — sab FREE, detailed, Hinglish mein.
Happy Prompting & Keep Building Dashboards! 🚀
💬 Comments (0)
Loading comments...