Power BI + DAX Complete Master Manual

1

Power BI Overview & Core Components

🇬🇧 English Definition

Power BI is Microsoft's enterprise Business Intelligence platform used for connecting to data sources, transforming raw data, creating relational data models, writing DAX calculations, and publishing interactive reporting dashboards.

🇮🇳 Hinglish Definition

Power BI raw data ko clean karke interactive charts, reports aur executive dashboards mein convert karta hai. Ye decision-making ko fast aur data-driven banata hai.

Component	Purpose / Kaam	Key Capability
Power BI Desktop	Create reports & data models	Authoring environment for Windows
Power Query	Clean and transform data	M-code engine for ETL data cleansing
Data Model	Define table relationships	Star schema / Snowflake relationships
DAX	Create business calculations	Formulas for measures and columns
Power BI Service	Publish and share reports	Cloud collaboration portal
Gateway / Refresh	Data refresh automation	Connects cloud reports to local databases

2

Power BI End-to-End Workflow Flowchart

🔄 Power BI End-to-End Workflow

Import Data
Excel / CSV / SQL

➔

Power Query
Clean + data types

➔

Data Model
Keys + relationships

➔

DAX Logic
Measures + columns

➔

Visuals & Service
Dashboard + publish

💡 Fundamental Rule

Power Query prepares data; DAX calculates; Visuals communicate results.

3

Power Query Data Cleaning Checklist

Power Query Feature	When to Use / Kab Use Karein	Action Required
Change Type	Correct date, number and text data types	Ensure dates are Date type, sales are Decimal/Fixed Number
Remove Duplicates	Only if duplicate rows are unwanted	Remove duplicate transaction IDs
Replace Values	Fix inconsistent labels or spelling mistakes	Replace "N/A" with 0 or fix "Lptop" to "Laptop"
Split Column	Split full name or address into separate fields	Split "First Last" by delimiter space
Merge Queries	Join tables using a common key	Equivalent to SQL JOIN between Fact and Dimension
Append Queries	Combine rows from similar structured tables	Equivalent to SQL UNION ALL across monthly files
Remove Errors	Only if removing error rows is appropriate	Clean bad rows prior to model loading

4

Practice Sales Dataset / Abhyas Data

OrderID	Date	Product	City	Qty	Sales	Cost
101	01-Jan-2026	Laptop	Mohali	2	100000	80000
102	03-Jan-2026	Mouse	Chandigarh	5	2500	1500
103	05-Jan-2026	Keyboard	Ludhiana	3	4500	2700
104	10-Feb-2026	Laptop	Chandigarh	1	50000	40000
105	15-Feb-2026	Monitor	Mohali	2	30000	24000
106	20-Mar-2026	Mouse	Ropar	10	5000	3000
107	05-Mar-2026	Laptop	Ludhiana	1	50000	40000
108	12-Mar-2026	Monitor	Chandigarh	3	45000	36000

5

Expected Totals & Data Import Steps

Metric	Result Value	Formula / Logic
Total Sales	₹2,87,000	SUM(Sales[Sales])
Total Cost	₹2,27,200	SUM(Sales[Cost])
Total Profit	₹59,800	Total Sales - Total Cost (₹2,87,000 - ₹2,27,200)
Rows / Orders	8	8 unique transaction rows
Unique Products / Cities	4 / 4	DISTINCTCOUNT of Product and City

🔄 Data Import Steps Flowchart

Open Power BI
Desktop App

➔

Get Data
Excel Workbook

➔

Navigator
Select Sales Sheet

➔

Transform Data
Power Query

➔

Close & Apply
Load to Model

6

Calculated Column vs Measure vs Calculated Table

// 1. Calculated Column (Row-Level Stored Value) Profit = Sales[Sales] - Sales[Cost] // Row 1 Example: ₹100,000 - ₹80,000 = ₹20,000 // 2. Measures (Interactive Aggregations recalculated in Filter Context) Total Sales = SUM(Sales[Sales]) Total Cost = SUM(Sales[Cost]) Total Profit = [Total Sales] - [Total Cost] Total Orders = DISTINCTCOUNT(Sales[OrderID]) // 3. Calculated Table (New Table Structure in Data Model) ProductList = DISTINCT(Sales[Product])

Feature	Calculated Column	Measure	Calculated Table
Output	Per-row value stored in RAM	Dynamic calculation result	New standalone table
Best For	Row-level filtering & slicers	KPIs, totals, card visuals	Date tables, helper dimensions
Filter Behavior	Stored static values	Recalculates on slicer changes	Depends on model relationships

🎯 Golden Rule of DAX Modeling

Use columns for row-level classification and slicing; use measures for interactive aggregations and KPIs; use calculated tables for structural data models.

7

DAX Symbols and Operators Reference Table

Symbol	Meaning / Purpose	Example Usage
+ / -	Addition / subtraction	10 + 5; 10 - 5
* / / / ^	Multiply / divide / power exponent	10 * 5; 2 ^ 3
=	Assignment or equality comparison	Total Sales = SUM(...)
==	Strict equality comparison (BLANK != 0)	BLANK() == 0 is FALSE
<>	Not equal to	Sales[City] <> "Ropar"
> / <	Greater than / less than	Sales[Sales] > 10000
>= / <=	Greater or equal / less or equal	Sales[Quantity] >= 2
&&	Logical AND (Both true)	Condition1 && Condition2
\|\|	Logical OR (At least one true)	Condition1 \|\| Condition2
&	Concatenate text strings	"City: " & Sales[City]
[Measure]	Measure reference syntax	[Total Sales]
Table[Column]	Column reference syntax	Sales[Cost]
()	Grouping arguments	SUM(Sales[Sales])
{}	Table constructor	{1, 2, 3}
VAR / RETURN	Define local variable / return output	VAR X = [Total Sales] RETURN X

8

Important Distinction: Column vs Measure Syntax & Logic

🇬🇧 English Distinction

`Sales[Sales]` refers to a specific column in the Sales table, whereas `[Total Sales]` refers to a measure. In DAX, `=` treats BLANK as zero, while `==` strictly checks equality.

🇮🇳 Hinglish Distinction

`Sales[Sales]` column reference hai aur `[Total Sales]` measure reference hai. DAX mein `=` BLANK ko 0 maanta hai jabki `==` strict check karta hai.

// Conditional Column Example Sales Status = IF(Sales[Sales] >= 50000, "High", "Low") // Compound Logical Expressions Premium Order = IF(Sales[Sales] > 10000 && Sales[Quantity] >= 2, "Premium", "Regular") Target City = IF(Sales[City] IN {"Mohali", "Ropar"}, "Target", "Other") Order Label = "Order ID: " & Sales[OrderID]

9

DAX Function Reference Table

Function	Purpose / Kaam	Example Formula
SUM	Add numeric values in a column	SUM(Sales[Sales])
AVERAGE	Calculate average value	AVERAGE(Sales[Sales])
MIN / MAX	Find minimum / maximum value	MAX(Sales[Sales])
COUNT	Count nonblank numeric values	COUNT(Sales[OrderID])
COUNTA	Count nonblank values (text + numbers)	COUNTA(Sales[Product])
COUNTROWS	Count total rows in a table	COUNTROWS(Sales)
DISTINCTCOUNT	Count distinct unique values	DISTINCTCOUNT(Sales[City])
IF	Conditional logical check	IF(test, true, false)
SWITCH	Multiple condition branch evaluation	SWITCH(TRUE(), test1, res1, default)
ISBLANK	Check if value is blank/null	ISBLANK([Total Sales])
COALESCE	Return first non-blank value	COALESCE([Total Sales], 0)
DIVIDE	Safe division handling divide-by-zero	DIVIDE([Total Profit], [Total Sales], 0)
ROUND / ABS	Round decimal / absolute value	ROUND(12.345, 2); ABS(-10)
LEFT/RIGHT/MID	Extract text characters	LEFT(Sales[Product], 3)
FORMAT	Format value as text string	FORMAT([Total Sales], "0,0")
YEAR/MONTH/DAY	Extract date parts	YEAR(Sales[Date])

// Conditional DAX Formulas Sales Band = SWITCH(TRUE(), Sales[Sales] >= 50000, "High", Sales[Sales] >= 10000, "Medium", "Low") Sales Check = IF(ISBLANK(Sales[Sales]), "Missing", "Available") Profit Margin % = DIVIDE([Total Profit], [Total Sales], 0)

⚠️ Percentage Formatting Rule

When using Power BI Percentage formatting tool on a Measure, DO NOT multiply by 100 inside the DAX formula! `DIVIDE([Total Profit], [Total Sales], 0)` automatically formats to 20.84%.

10

Iterator Functions Concept & Execution Model

🇬🇧 English Definition

Iterator functions ending in 'X' (such as SUMX, AVERAGEX, RANKX) iterate row-by-row through a table, evaluate an expression for each row, and then aggregate the results.

🇮🇳 Hinglish Definition

Iterator functions har row par jaakar calculation karte hain aur baad mein un sabhi row-level results ko sum, average ya rank karte hain.

Total Profit X = SUMX(Sales, Sales[Sales] - Sales[Cost])

🔄 SUMX Row-by-Row Execution Flowchart

Take Row 1
OrderID 101

➔

Calculate Expression
100000 - 80000 = 20000

➔

Repeat All Rows
Rows 1 to 8

➔

Sum Results
Total Profit = ₹59,800

11

Iterator Functions Reference Table & SUM vs SUMX

Function	Use / Kaam	Syntax Example
SUMX	Sum evaluated row expression	SUMX(Sales, Sales[Sales] - Sales[Cost])
AVERAGEX	Average evaluated row expression	AVERAGEX(Sales, Sales[Sales] - Sales[Cost])
COUNTX	Count nonblank numeric expression results	COUNTX(Sales, Sales[Sales] - Sales[Cost])
RANKX	Rank items by expression	RANKX(ALL(Sales[Product]), [Total Sales],, DESC, DENSE)

🎯 SUM vs SUMX Interview Difference

SUM: Operates on a single numeric column directly (`SUM(Sales[Sales])`).
SUMX: Accepts a table and an expression, evaluates the expression row-by-row, and sums the results (`SUMX(Sales, Sales[Sales] - Sales[Cost])`).

12

RANKX Deep Dive & Ties Handling

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

🔍 RANKX Parameter Breakdown

`ALL(Sales[Product])`: Table argument clearing product filters to rank all products against each other.
`[Total Sales]`: Expression used to order rankings.
`DESC`: Highest sales value receives Rank 1.
`DENSE`: Handles ties without skipping rank numbers (e.g. 1, 2, 2, 3 instead of 1, 2, 2, 4).

13

CALCULATE and FILTER Mechanics

🇬🇧 English Definition

CALCULATE is the most powerful function in DAX. It evaluates an expression in a modified filter context. FILTER returns a table of matching rows and is passed into CALCULATE for complex filters.

🇮🇳 Hinglish Definition

CALCULATE existing filter context ko change ya override karke formula calculate karta hai. FILTER function condition matching rows ki table return karta hai.

// CALCULATE Simple Filter Modification Mohali Sales = CALCULATE([Total Sales], Sales[City] = "Mohali") // Expected Output: ₹1,30,000 // CALCULATE with FILTER Function High Value Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Sales] > 10000))

14

Filter Modifiers & Sales Share %

Function	Purpose / Kaam
CALCULATE	Evaluate expression in modified filter context
FILTER	Return table matching logical condition
ALL	Clear all filters from table or column
REMOVEFILTERS	Explicitly clear filters from specified table/column
ALLEXCEPT	Clear all filters except specified columns
KEEPFILTERS	Intersect new filter with existing filter context
ALLSELECTED	Respect visual slicers while ignoring row grouping filters

// Sales Share % (City Contribution to Total Sales) Sales Share % = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Sales[City])), 0)

🔍 REMOVEFILTERS Behavior

`REMOVEFILTERS(Sales[City])` clears the City filter to compute total company sales while preserving Date or Product slicer selections!

15

Row Context vs Filter Context vs Context Transition

Concept	Example	Explanation
Row context	Profit = Sales[Sales] - Sales[Cost]	Knows current row values automatically during iteration
Filter context	Total Sales = SUM(Sales[Sales])	Active slicers, report filters, and visual rows filtering data
Context transition	CALCULATE() inside row context	Converts current row context into equivalent filter context

City Filter Context Breakdown Table

Selection / City	Total Sales Output
No city filter (Overall)	₹2,87,000
Mohali	₹1,30,000
Chandigarh	₹97,500
Ludhiana	₹54,500
Ropar	₹5,000

16

VAR and RETURN & Virtual Tables

// Variables for readability & performance Profit Margin % = VAR Revenue = [Total Sales] VAR ProfitValue = [Total Profit] RETURN DIVIDE(ProfitValue, Revenue, 0)

Virtual Table Function	Purpose / Kaam
VALUES / DISTINCT	Return distinct single-column table
ADDCOLUMNS	Add calculated columns to a table expression
SUMMARIZE	Create a grouped summary table (like SQL GROUP BY)
SELECTCOLUMNS	Return selected or renamed columns
TOPN	Return top N rows based on expression
CROSSJOIN	Create Cartesian product combinations between tables
UNION / INTERSECT / EXCEPT	Combine or compare table rows

Product Summary = SUMMARIZE(Sales, Sales[Product], "Product Sales", SUM(Sales[Sales]))

17

Calendar Table Setup & Best Practices

🇬🇧 English Definition

Time Intelligence in DAX requires a dedicated continuous Date table marked as Date Table in Power BI, connected via a 1-to-Many relationship to the Fact table Date column.

🇮🇳 Hinglish Definition

Time intelligence formulas (MTD, YTD, YoY) ke sahi kaam karne ke liye ek alag Date table banana aur use 'Mark as Date Table' set karna zaroori hai.

// 1. Create Date Table DateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date])) // 2. Add Calculated Columns for Sorting & Slicing Year = YEAR(DateTable[Date]) Month Number = MONTH(DateTable[Date]) Month Name = FORMAT(DateTable[Date], "MMM")

⚠️ Month Name Sorting Rule

Always select 'Month Name' column and click 'Sort by Column' ➔ 'Month Number'. This prevents alphabetical sorting (Apr, Aug, Dec) and enforces calendar sequence (Jan, Feb, Mar)!

18

Time Intelligence Functions & Growth Formulas

Measure / Function	Syntax Example	Meaning / Purpose
MTD	TOTALMTD([Total Sales], DateTable[Date])	Month-to-date cumulative sales
YTD	TOTALYTD([Total Sales], DateTable[Date])	Year-to-date cumulative sales
Previous Month	CALCULATE([Total Sales], PREVIOUSMONTH(DateTable[Date]))	Sales from previous month
Previous Year	CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DateTable[Date]))	Sales from same period last year
Growth	[Total Sales] - [Previous Month Sales]	Absolute monetary sales growth
Growth %	DIVIDE([Sales Growth], [Previous Month Sales], 0)	Relative percentage sales growth

Sales MTD = TOTALMTD([Total Sales], DateTable[Date]) Sales YTD = TOTALYTD([Total Sales], DateTable[Date]) Previous Month Sales = CALCULATE([Total Sales], PREVIOUSMONTH(DateTable[Date])) Sales Growth = [Total Sales] - [Previous Month Sales] Sales Growth % = DIVIDE([Sales Growth], [Previous Month Sales], 0)

19

Advanced Business Measures

// 1. Product Contribution Percentage Product Sales % = DIVIDE([Total Sales], CALCULATE([Total Sales], REMOVEFILTERS(Sales[Product])), 0) // 2. Top Products Ranking Product Rank = RANKX(ALL(Sales[Product]), [Total Sales],, DESC, DENSE) // 3. Target Achievement Total Target = SUM(SalesTarget[TargetAmount]) Target Achievement % = DIVIDE([Total Sales], [Total Target], 0) Target Status = IF([Total Sales] >= [Total Target], "Target Achieved", "Below Target") // 4. Running Total / Cumulative Sales Running Total = CALCULATE(SUM(Sales[Sales]), FILTER(ALLSELECTED(DateTable[Date]), DateTable[Date] <= MAX(DateTable[Date]))) // 5. Dynamic Chart Title Sales Chart Title = "Sales Performance - " & SELECTEDVALUE(Sales[City], "All Cities")

20

Power BI Visualizations Pane & Visual Selection Guide (Icons & Symbols)

🇬🇧 Visual Selection Rule

Power BI provides a distinct visual icon in the Visualizations Pane for each chart type. Select Clustered Bars for comparisons, Line charts for trends over time, Donut/Pie for composition (<= 5 categories), Scatter for correlation, and Gauges/KPI Cards for metric targets.

🇮🇳 Hinglish Explanation

Power BI Desktop Visualizations Pane mein har visual chart ka specific symbol hota hai. Sahi visual icon select karna report clarity, dashboard presentation aur interview explanation ke liye zaroori hai.

Icon / Symbol	Visual Type	Best Use / Kab Use Karein	Typical Fields
📊	Clustered bar/column	Compare categories side-by-side	Product + Sales
📶	Stacked bar/column	Total & category breakdown	Region + Category + Sales
💯	100% stacked bar	Percentage composition comparison	Month + Category + Sales
📈	Line chart	Trend over time	Date + Sales
⛰️	Area chart	Magnitude volume over time	Date + Sales
📊📈	Combo chart	Sales with target overlay	Month + Sales + Target
🪜	Waterfall chart	Increase/decrease bridge analysis	Revenue, costs, profit
🔻	Funnel chart	Process stages conversion	Stage + count
🔵	Scatter plot	Relationship between 2 variables	Sales + Profit
🍩	Pie / Donut chart	Share of few categories (<= 5)	Category + Sales
🧱	Treemap	Contribution by nested rectangles	Product + Sales
🗺️	Map / Azure Maps	Geographic spatial analysis	City/State or Lat/Long
⏱️	Gauge / KPI	Target achievement metrics	Actual vs Target
▦	Table / Matrix	Detailed row data / Pivot summary	OrderID, Category, Sales
🌳	Decomposition Tree	Root cause driver exploration	Sales + Dimensions
🎴	Card / Multi-row card	Single KPI or metric list summary	Total Sales / Revenue
🎛️	Slicer	Interactive data filtering control	Year, Region, Category

21

Dashboard Building 8-Step Flowchart & Core KPIs

🔄 Dashboard Creation Process Flowchart

1. Import & Clean
Power Query

➔

2. Model Keys
Relationships

➔

3. Core Measures
DAX KPIs

➔

4. KPI Cards
Top metrics

➔

5. Slicers & Charts
Bar/Line/Donut

➔

6. Publish
Service Report

22

Common DAX Errors & Troubleshooting Table

Problem / Error	Possible Cause	Fix / Solution
Single value cannot be determined	Passing a column to measure without aggregation	Wrap column in SUM(), MAX(), or SELECTEDVALUE()
Incorrect totals in visual	Measure ignoring table grain or filter context	Use SUMX() or adjust CALCULATE filter modifiers
Blank result	Date table disconnect or missing filter match	Check relationships & date table coverage
Divide by zero error	Standard division `/` by 0	Use `DIVIDE(num, denom, 0)`
Month names out of order	Alphabetical sorting default	Sort Month Name column by Month Number
Percentage 100x too high	Multiplied formula by 100 while applying % format	Remove `* 100` from formula
Previous year blank	Missing prior year dates in Date table	Expand CALENDAR date range
Circular dependency	Calculated columns referencing each other	Refactor to measures or review model design

23

12 Practice Assignments with Hints & Formulas

No.	Task / Assignment	Hint / Formula
1	Total sales	SUM(Sales[Sales])
2	Total cost	SUM(Sales[Cost])
3	Total profit	[Total Sales] - [Total Cost]
4	Profit margin	DIVIDE([Total Profit], [Total Sales], 0)
5	Label sales >= 50000 as High	IF(Sales[Sales] >= 50000, "High", "Low")
6	Mohali sales	CALCULATE([Total Sales], Sales[City] = "Mohali")
7	Unique cities count	DISTINCTCOUNT(Sales[City])
8	Count total rows	COUNTROWS(Sales)
9	Sum sales > 10000 rows	CALCULATE([Total Sales], FILTER(Sales, Sales[Sales] > 10000))
10	Top 5 products	RANKX or visual Top N filter
11	Previous month sales	PREVIOUSMONTH + CALCULATE
12	Running total	CALCULATE + FILTER + ALLSELECTED

24

50+ Power BI & DAX Master Interview Q&A

❓ Core Concept Interview Q&A

Q: What is the difference between Power Query and DAX? Power Query is an ETL tool used for data cleaning and transformation before loading into the model; DAX is a calculation engine used for dynamic reporting, measures, and business analytics.

Q: Difference between Calculated Column and Measure? A column calculates values row-by-row at refresh time and stores them in RAM; a measure calculates on-the-fly inside the active visual filter context.

Q: How does CALCULATE work? `CALCULATE` evaluates an expression in a modified filter context by adding, removing, or updating active table filters.

Q: What is Context Transition? Context Transition occurs when `CALCULATE()` is invoked inside a row context (like SUMX or a Calculated Column), converting the current row into an equivalent filter context.