1. What is DAX
DAX stands for: Data Analysis Expressions
Used for: Measures, Calculated Columns, Calculated Tables, KPIs, Business Logic
Examples
Calculate Sales
Total Sales = SUM(Sales[SalesAmount])
Calculate Profit
Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])
📌 2. Measures vs Calculated Columns
One of the most common interview questions.
🔹 Calculated Column
Calculated row by row.
Stored physically inside the model.
Example: Profit = Sales[Revenue] - Sales[Cost]
Characteristics: Stored in memory, Uses Row Context, Increases model size
🔹 Measure
Calculated dynamically.
Example: Total Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost])
Characteristics: Dynamic, Faster, Smaller model size
Interview Answer
Prefer: ✅ Measures instead of ❌ Calculated Columns whenever possible.
📌 3. Understanding Row Context
Row Context means: DAX evaluates one row at a time.
Example
Revenue Cost 1000 700
Formula: Profit = Sales[Revenue] - Sales[Cost]
Result: 300 Each row is calculated independently.
📌 4. Understanding Filter Context
Filter Context is created by: Slicers, Filters, Visuals
Example: User selects Region = West
Measure: Total Sales = SUM(Sales[Amount])
Now DAX only calculates West Region Sales.
Interview Tip Row Context → Row-by-row Filter Context → Filtered aggregation
📌 5. Basic Aggregation Functions
SUM Total Sales = SUM(Sales[Amount])
AVERAGE Average Sales = AVERAGE(Sales[Amount])
COUNT Order Count = COUNT(Sales[OrderID])
COUNTROWS Total Rows = COUNTROWS(Sales)
MIN Minimum Sales = MIN(Sales[Amount])
MAX Maximum Sales = MAX(Sales[Amount])
📌 6. CALCULATE Most Important Function
Most interview questions involve CALCULATE.
Purpose: Changes filter context.
Syntax:
CALCULATE(
Expression,
Filter
)
Example:
West Sales =
CALCULATE(
SUM(Sales[Amount]),
Sales[Region] = "West"
)
Result: Only West Region Sales.
Interview Tip If asked "Most important DAX function" Answer: ✅ CALCULATE
📌 7. FILTER
Creates custom filters.
Example:
High Value Sales =
CALCULATE(
SUM(Sales[Amount]),
FILTER(
Sales,
Sales[Amount] > 1000
)
)
Usage: Advanced filtering, Conditional calculations, Dynamic KPIs
📌 8. IF Function
Similar to Excel.
Example:
Sales Category =
IF(
Sales[Amount] > 1000,
"High",
"Low"
)
📌 9. SWITCH Function
Cleaner than multiple IFs.
Example:
Grade =
SWITCH(
TRUE(),
Sales[Amount] >= 5000, "Excellent",
Sales[Amount] >= 2000, "Good",
"Average"
)
📌 10. DISTINCTCOUNT
Counts unique values.
Example:
Unique Customers =
DISTINCTCOUNT(
Customers[CustomerID]
)
Business Example: Count Unique Customers, Unique Orders, Active Users
📌 11. Time Intelligence
One of the biggest strengths of DAX.
Requirement: Proper Date Table
YTD Year-to-Date
YTD Sales = TOTALYTD(SUM(Sales[Amount]), Date[Date])
MTD Month-to-Date
MTD Sales = TOTALMTD(SUM(Sales[Amount]), Date[Date])
QTD Quarter-to-Date
QTD Sales = TOTALQTD(SUM(Sales[Amount]), Date[Date])
📌 12. Previous Year Comparison
Last Year Sales =
CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Date[Date])
)
Business Usage: Compare 2025 vs 2024, Monthly trends, Growth analysis
📌 13. ALL Function
Removes filters.
Example:
Total Company Sales =CALCULATE(
SUM(Sales[Amount]),
ALL(Sales)
Usage: Percent of Total, Benchmark calculations
📌 14. Variables VAR
Very important for advanced DAX.
Example:
Profit Margin =
VAR Profit = SUM(Sales[Profit])
VAR Revenue = SUM(Sales[Revenue])
RETURN DIVIDE(Profit, Revenue)
Benefits: Faster, Easier debugging, Cleaner code
📌 15. DIVIDE Function
Preferred over "/"
Example:
Profit Margin =
DIVIDE(
SUM(Sales[Profit]),
SUM(Sales[Revenue])
)
Benefit: Avoids divide-by-zero errors.
📌 16. Common DAX Interview Questions
Beginner
1. What is DAX
2. Difference between Measure and Calculated Column
3. What is Filter Context
4. What is Row Context
5. What is CALCULATE
Intermediate
6. What is FILTER
7. What is ALL
8. What is DISTINCTCOUNT
9. What are Variables
10. What is Time Intelligence
📌 17. Common DAX Mistakes
❌ Too many calculated columns
❌ Ignoring filter context
❌ Not using VAR
❌ Complex nested IFs
❌ Poor data modeling
📌 18. Real-World KPI Measures
Revenue Revenue = SUM(Sales[Revenue])
Profit Profit = SUM(Sales[Profit])
Profit Margin % Profit Margin = DIVIDE([Profit], [Revenue])
Growth % Growth % = DIVIDE([Revenue] - [Last Year Revenue], [Last Year Revenue])
Real-World Power BI Scenario-Based Interview Questions with Answers
📊 Question 1: Calculate Year-over-Year YoY Sales Growth
Table: Sales Date, Revenue
Requirement: Compare current year's revenue with previous year's revenue.
DAX:
YoY Growth % =
VAR CurrentSales = SUM(Sales[Revenue])
VAR PreviousSales =
CALCULATE(
SUM(Sales[Revenue]),
SAMEPERIODLASTYEAR(Sales[Date])
)
RETURN
DIVIDE(CurrentSales - PreviousSales, PreviousSales, 0) * 100
📊 Question 2: Find Top 5 Customers by Revenue
Table: Sales CustomerID, Revenue
Requirement: Display only the top 5 customers based on total revenue.
DAX:
Customer Rank =
RANKX(
ALL(Sales[CustomerID]),
CALCULATE(SUM(Sales[Revenue])),
,
DESC
)
Apply Visual Filter: Customer Rank <= 5
Power BI for Data Analytics: Question 3: Calculate Running Total Sales
Table: Sales Date, Revenue
Requirement: Show cumulative sales over time.
DAX:
Running Total =
CALCULATE(
SUM(Sales[Revenue]),
FILTER(
ALLSELECTED(Sales[Date]),
Sales[Date] <= MAX(Sales[Date])
)
)
📊 Question 4: Calculate Customer Retention Rate
Tables: Customers CustomerID, Orders CustomerID, OrderDate
Requirement: Find customers who purchased in both current and previous periods.
DAX:
Retained Customers =
CALCULATE(
DISTINCTCOUNT(Orders[CustomerID]),
INTERSECT(
VALUES(Orders[CustomerID]),
CALCULATETABLE(
VALUES(Orders[CustomerID]),
PREVIOUSMONTH('Calendar'[Date])
)
)
)
📊 Question 5: Revenue Contribution %
Table: Sales Product, Revenue
Requirement: Calculate percentage contribution of each product to total revenue.
DAX:
Revenue Contribution % =
DIVIDE(
SUM(Sales[Revenue]),
CALCULATE(
SUM(Sales[Revenue]),
ALL(Sales[Product])
)
) * 100
📊 Question 6: Identify Inactive Customers
Table: Orders CustomerID, OrderDate
Requirement: Customers who have not purchased in the last 90 days.
DAX:
Inactive Customer =
IF(
DATEDIFF(
MAX(Orders[OrderDate]),
TODAY(),
DAY
) > 90,
"Inactive",
"Active"
)
📊 Question 7: Dynamic Sales Target Achievement
Tables: Sales Revenue, Targets TargetAmount
Requirement: Compare actual sales with target.
DAX:
Achievement % =
DIVIDE(
SUM(Sales[Revenue]),
SUM(Targets[TargetAmount]),
0
) * 100
📊 Question 8: Month-over-Month MoM Growth
Table: Sales Date, Revenue
DAX:
MoM Growth % =
VAR CurrentMonth = SUM(Sales[Revenue])
VAR PreviousMonth =
CALCULATE(
SUM(Sales[Revenue]),
PREVIOUSMONTH('Calendar'[Date])
)
RETURN
DIVIDE(CurrentMonth - PreviousMonth, PreviousMonth, 0) * 100
📊 Question 9: Calculate Average Order Value AOV
Table: Orders OrderID, Revenue
DAX:
AOV =
DIVIDE(
SUM(Orders[Revenue]),
DISTINCTCOUNT(Orders[OrderID])
)
📊 Question 10: Detect Sales Anomalies
Table: Sales Date, Revenue
Requirement: Flag days where sales are 50% above average.
DAX:
Sales Anomaly =
IF(
SUM(Sales[Revenue]) > AVERAGE(Sales[Revenue]) * 1.5,
"Anomaly",
"Normal"
)
Double Tap ❤️ For More
[10:29 AM, 6/26/2026] Power BI for Data Analytics: 🚀 Power BI Roadmap — Topic 9
☁️ Power BI Service, Collaboration & Deployment
Building a report in Power BI Desktop is only half the job.
In real organizations, reports need to be:
Published, Shared, Secured, Refreshed automatically, Managed by teams
This is where Power BI Service comes in.
🎯 Learning Objectives
By the end of this topic, you will be able to:
✅ Publish reports
✅ Create workspaces
✅ Share dashboards
✅ Configure Row-Level Security RLS
✅ Schedule data refresh
✅ Configure gateways
✅ Deploy reports to production
📌 1. What is Power BI Service
Power BI Service is Microsoft's cloud platform for sharing, managing, and collaborating on Power BI reports.
Power BI Desktop is where you build reports.
Power BI Desktop is where you build reports.
Power BI Service is where users consume those reports.
📌 2. Power BI Desktop vs Power BI Service
Power BI Desktop Power BI Service
Create reports Share reports
Build models Collaborate with teams
Write DAX Schedule refresh
Design dashboards Manage security
👉 Desktop = Development
👉 Service = Deployment & Collaboration
📌 3. Workspaces
A Workspace is a collaborative area where teams develop and manage Power BI content.
A workspace stores: Reports, Dashboards, Datasets, Dataflows
Typical Workspaces
Sales Workspace, Finance Workspace, HR Workspace, Marketing Workspace
Workspace Roles
Role Permission
Admin Full control
Member Edit & publish
Contributor Create content
Viewer View only
📌 4. Publishing Reports
Steps
1. Save PBIX
2. Click Publish
3. Login
4. Select Workspace
5. Publish
Now the report is available in Power BI Service.
📌 5. Datasets
A Dataset is the data model behind a report.
One dataset can support multiple reports.
Example: Sales Dataset → Sales Dashboard → Executive Dashboard → Regional Dashboard
This avoids duplicate data models.
📌 6. Reports vs Dashboards
Report
Multiple pages, Interactive, Built in Power BI Desktop
Dashboard
Single page, KPI summary, Built in Power BI Service, Can combine visuals from multiple reports
Example
Sales Dashboard Contains: Revenue Card, Sales Trend, Profit KPI, Customer Count
📌 7. Apps
Apps package reports and dashboards for business users.
Instead of sharing many reports individually: Workspace → Create App → Users install App
Benefits: ✅ Easy distribution, ✅ Better user experience, ✅ Centralized updates
📌 8. Sharing Reports
Methods
✅ Share button, ✅ Workspace access, ✅ Apps, ✅ Microsoft Teams
Always provide users with the correct permissions.
📌 9. Row-Level Security RLS
One of the most important security features.
Example
Manager A Can only view North Region
Manager B Can only view South Region
Same report. Different data.
Types
Static RLS, Dynamic RLS
Benefits
✅ Data privacy, ✅ Compliance, ✅ Personalized reporting
📌 10. Scheduled Refresh
Instead of manually refreshing data every day: Power BI refreshes automatically.
Example
Refresh Every day 8:00 AM
Reports always show current data.
📌 11. Data Gateway
Used when data is stored on-premises.
Example: SQL Server inside company network.
Power BI Service → Gateway → SQL Server
The gateway securely transfers data.
Types
Standard Gateway, Personal Gateway
Enterprise environments usually use the Standard Gateway.
📌 12. Data Refresh Types
Manual Refresh
User clicks Refresh.
Scheduled Refresh
Automatic refresh at scheduled times.
Incremental Refresh
Only new data is refreshed. Useful for large datasets.
📌 13. Permissions
Power BI provides different access levels.
Examples
Can View, Can Build, Can Share, Can Reshare
Grant only the permissions users actually need.
📌 14. Deployment Pipeline
Enterprise organizations usually maintain three environments.
Development → Testing → Production
This ensures reports are tested before reaching business users.
📌 15. Version Control
Maintain different versions of reports.
Benefits
✅ Rollback, ✅ Collaboration, ✅ Change tracking
Many teams use Git for storing PBIX files and documentation.
📌 16. Monitoring Usage
Power BI Service provides usage metrics.
Track: Report views, Dashboard views, Active users, Popular reports
This helps identify which reports deliver the most value.
📌 17. Notifications & Alerts
Create alerts for KPI cards.
Example
Notify manager if Sales < ₹5,00,000
Users receive notifications automatically.
📌 18. Real-World Deployment Example
A retail company develops a Sales Dashboard.
Development: Analyst builds report.
Testing: Business validates calculations.
Production: Report is published.
Users: Managers access dashboards through the Power BI App.
Daily: Scheduled refresh updates data automatically.
📌 19. Common Mistakes
❌ Publishing directly to production
❌ Giving everyone Admin access
❌ No Row-Level Security
❌ Manual refresh every day
❌ No testing
❌ Poor workspace organization
📌 20. Interview Questions
1. What is Power BI Service
2. Difference between Report and Dashboard
3. What is a Workspace
4. What is a Dataset
5. What is Row-Level Security
6. What is a Gateway
7. What is Scheduled Refresh
8. What is Incremental Refresh
9. What are Apps
10. Explain the deployment pipeline
🎯 Goal of This Topic
After completing this topic, you should be able to:
✅ Publish reports
✅ Share dashboards securely
✅ Configure workspaces
✅ Implement RLS
✅ Schedule automatic refreshes
✅ Deploy reports to production