With Real-Time Scenarios, Architecture, Performance Tuning, DAX, Data Modeling, Security, and Enterprise Use Cases

This guide is designed for:

• Power BI Developer
• BI Engineer
• Data Analyst
• Data Engineer using Power BI
• Azure Data Engineer + Power BI
• Senior Power BI Consultant
• Power BI Architect

1. What is Power BI?

Answer

Power BI is a Microsoft Business Intelligence platform used for:

• Data Integration
• Data Transformation
• Data Modeling
• Data Visualization
• Self-Service Analytics
• Enterprise Reporting

It helps organizations convert raw data into interactive dashboards and reports.

Components

2. Explain Power BI Architecture

Answer

Source Systems
     ↓
Power Query
     ↓
Data Model
     ↓
DAX Calculations
     ↓
Reports
     ↓
Power BI Service
     ↓
Users

Real-Time Scenario

Retail Company:

Data Sources:

• SAP
• SQL Server
• Salesforce
• Excel

Flow:

SAP → Power Query
SQL → Power Query
Salesforce → Power Query

→ Data Model

→ Dashboards

→ Executives

3. What is Power Query?

Answer

Power Query is ETL engine inside Power BI.

Used for:

• Extract
• Transform
• Clean
• Load

Common Operations

• Remove duplicates
• Merge tables
• Append tables
• Split columns
• Data type conversion
• Null handling

Real-Time Scenario

Customer file contains:

Power Query:

• Remove duplicates
• Replace NULL
• Standardize formats

before loading.

4. Difference Between Power Query and DAX

Interview Answer

Use Power Query whenever possible.

Use DAX only when transformation cannot be achieved before loading.

5. What is DAX?

Answer

DAX (Data Analysis Expressions) is formula language used in Power BI.

Used for:

• Measures
• Calculated Columns
• Calculated Tables

Example:

Total Sales =
SUM(Sales[Amount])

6. Calculated Column vs Measure

Calculated Column

Profit =
Sales[Revenue]-Sales[Cost]

Stored physically.

Measure

Total Profit =
SUM(Sales[Revenue])-SUM(Sales[Cost])

Calculated dynamically.

Interview Question

Which consumes more memory?

Answer:
Calculated Columns.

7. What is Star Schema?

Answer

Most important data model in Power BI.

          Product
             |
Customer -- FactSales -- Date
             |
          Region

Benefits

• Faster performance
• Easy maintenance
• Better compression

Real-Time Scenario

E-commerce Dashboard

Fact Table:

• Orders

Dimension Tables:

• Customer
• Product
• Date
• Geography

8. Difference Between Star and Snowflake Schema

Best Practice

Power BI prefers Star Schema.

9. What is a Fact Table?

Answer

Contains measurable business events.

Example:

10. What is a Dimension Table?

Answer

Contains descriptive attributes.

Example:

Customer Table

11. What are Relationships?

Answer

Connection between tables.

Types:

• One to Many
• Many to One
• One to One
• Many to Many

12. What is Active Relationship?

Answer

Power BI allows only one active relationship between two tables.

Active relationship shown as:

Solid Line

13. What is Inactive Relationship?

Answer

Shown as:

Dotted Line

Activated using:

USERELATIONSHIP()

Example:

Order Date vs Ship Date.

14. Explain Filter Context

Answer

Filter Context means data visible after applying filters.

Example:

Region = USA

Measure:

SUM(Sales[Amount])

Calculates only USA sales.

15. Explain Row Context

Answer

Applies row-by-row calculations.

Example:

Profit =
Sales[Revenue]-Sales[Cost]

Each row evaluated separately.

16. What is Context Transition?

Answer

Occurs when row context becomes filter context.

Functions:

CALCULATE()

creates context transition.

17. What is CALCULATE()?

Answer

Most important DAX function.

Changes filter context.

Example:

Sales USA =
CALCULATE(
SUM(Sales[Amount]),
Sales[Country]="USA"
)

18. Difference Between SUM and SUMX

SUM

SUM(Sales[Amount])

SUMX

SUMX(
Sales,
Sales[Qty]*Sales[Price]
)

Interview Answer

SUMX iterates row by row.

19. What is ALL()?

Answer

Removes filters.

Example:

Sales % =
DIVIDE(
SUM(Sales[Amount]),
CALCULATE(
SUM(Sales[Amount]),
ALL(Sales)
)
)

20. What is RELATED()?

Answer

Fetches value from related table.

Example:

RELATED(Customer[Name])

21. What is LOOKUPVALUE()?

Answer

Performs lookup like VLOOKUP.

Example:

LOOKUPVALUE(
Customer[Name],
Customer[ID],
Sales[CustomerID]
)

22. What is Incremental Refresh?

Answer

Refreshes only new data.

Instead of:

100 Million Rows

refreshing every day,

Power BI refreshes:

Yesterday's Data

only.

Real-Time Scenario

Banking Transactions:

• 5 years data
• Daily 2 million new records

Incremental Refresh reduces refresh from hours to minutes.

23. Import Mode vs DirectQuery

Real-Time Scenario

Import Mode

Daily Sales Dashboard

Refresh once daily.

DirectQuery

Stock Market Dashboard

Needs live data.

24. What is Composite Model?

Answer

Combination of:

• Import
• DirectQuery

within same model.

25. What is Aggregation Table?

Answer

Pre-calculated summarized table.

Example:

Instead of:

1 Billion Rows

use:

Monthly Sales Summary

26. How to Improve Power BI Performance?

Interview Favorite

1. Use Star Schema

2. Remove Unused Columns

3. Avoid Bidirectional Filters

4. Reduce Cardinality

5. Use Measures Instead of Columns

6. Optimize DAX

7. Use Aggregations

8. Incremental Refresh

9. Disable Auto Date Table

10. Optimize Visuals

27. What is Cardinality?

Answer

Number of unique values.

Example:

Customer ID

10 Million Unique Values

High Cardinality.

Impacts memory.

28. What is Row Level Security (RLS)?

Answer

Restricts data access.

Example

Manager A:

Can see only USA.

Manager B:

Can see only Canada.

DAX

[Country]="USA"

29. Dynamic RLS Scenario

Table:

Rule:

User[Email] = USERPRINCIPALNAME()

User automatically sees own region.

30. What is Gateway?

Answer

Gateway connects:

On-Prem Data Sources

with

Power BI Service.

Types

• Personal Gateway
• Enterprise Gateway

Enterprise preferred.

31. Explain Deployment Pipeline

Answer

Used for Dev → Test → Prod migration.

Stages:

Development
      ↓
Test
      ↓
Production

32. What is Dataflow?

Answer

Reusable ETL layer in Power BI Service.

Real-Time Scenario

20 reports need customer data.

Instead of duplicating transformations:

Create one Dataflow.

All reports reuse it.

33. What is Power BI Fabric?

Answer

Microsoft’s unified analytics platform.

Includes:

• Data Factory
• Data Engineering
• Data Warehouse
• Data Science
• Real-Time Analytics
• Power BI

34. Explain End-to-End Power BI Project

Interview Scenario

Requirement

Build Executive Sales Dashboard.

Sources

• SAP
• Oracle
• Salesforce
• Excel

Solution

Step 1

Power Query:

• Clean Data
• Standardize

Step 2

Build Star Schema

Fact:

• Sales

Dimensions:

• Product
• Customer
• Date

Step 3

Create Measures

Revenue
Profit
Margin
YoY Growth

Step 4

Implement RLS

Regional Managers see own data.

Step 5

Incremental Refresh

Last 3 years.

Step 6

Deploy Pipeline

Dev → Test → Prod

Step 7

Schedule Refresh

Daily.

Top 20 Advanced Power BI Interview Questions

Q1. Explain VertiPaq Engine.

Answer:
Columnar in-memory storage engine that compresses data for high-speed analytics.

Q2. Explain Formula Engine vs Storage Engine.

Storage Engine:

• Retrieves data.

Formula Engine:

• Executes DAX.

Q3. What is Query Folding?

Answer:

Power Query pushes transformations back to source DB.

Example:

WHERE OrderDate > '2025'

executed in SQL Server instead of Power BI.

Improves performance dramatically.

Q4. What breaks Query Folding?

• Custom M functions
• Complex transformations
• Certain merges

Q5. Explain Bidirectional Filtering.

Answer:

Filters propagate both directions.

Can create ambiguity and performance issues.

Q6. How would you optimize a 500M row model?

Answer:

• Star schema
• Aggregation tables
• Incremental refresh
• Partitioning
• Remove unused columns
• Composite models

Q7. How do you troubleshoot slow reports?

Answer:

Use:

• Performance Analyzer
• DAX Studio
• VertiPaq Analyzer
• Query Diagnostics

Q8. What is Calculation Group?

Answer:

Reusable DAX logic.

Example:

One calculation applied to:

• MTD
• QTD
• YTD

Q9. What is Tabular Model?

Answer:

Semantic model behind Power BI datasets.

Q10. Difference Between Dataset and Dataflow?

Dataset:
Analytics layer.

Dataflow:
ETL layer.

Real Enterprise Scenario Questions

Scenario 1

Dashboard refresh takes 4 hours.

Solution:

• Incremental Refresh
• Query Folding
• Aggregations
• Remove unused columns

Scenario 2

Users see incorrect totals.

Solution:

• Validate relationships
• Check filter context
• Verify DAX measures

Scenario 3

Power BI consuming huge memory.

Solution:

• Reduce cardinality
• Remove calculated columns
• Use measures
• Optimize model

Scenario 4

Need near real-time dashboard.

Solution:

• DirectQuery
• Streaming Dataset
• Event Hub
• Fabric Real-Time Analytics

Scenario 5

CEO wants mobile dashboard.

Solution:

• Mobile Layout
• Responsive visuals
• KPI cards

Senior/Architect-Level Questions

How would you design Power BI for 10,000 users?

Answer:

Architecture:

Azure Data Lake
       ↓
ADF
       ↓
Snowflake/SQL DW
       ↓
Power BI Semantic Model
       ↓
RLS
       ↓
Power BI Premium Capacity

Use:

• Premium Capacity
• Incremental Refresh
• Deployment Pipelines
• Dataflows
• Aggregations
• Governance Framework

Most Frequently Asked Power BI Interview Questions

1. What is Power BI?
2. Import vs DirectQuery?
3. Star Schema?
4. DAX vs Power Query?
5. CALCULATE()?
6. Measure vs Calculated Column?
7. RLS?
8. Incremental Refresh?
9. Query Folding?
10. Composite Models?
11. Aggregations?
12. Gateway?
13. Dataflows?
14. Performance Tuning?
15. Relationship Types?
16. Context Transition?
17. ALL() Function?
18. USERELATIONSHIP()?
19. VertiPaq Engine?
20. Enterprise Architecture?

These are among the highest-priority Power BI interview topics commonly asked for Mid-Level, Senior, Lead, Architect, Data Engineer, BI Engineer, and Analytics Engineer roles in the U.S. market.

Here is a comprehensive guide to Power BI interview questions and answers, categorized by difficulty, with real-time scenarios and detailed explanations. Prioritize the high-priority topics marked with ⭐.

1. Basic Questions (But Often Asked with a Twist)

Q1: What is the difference between Power BI Desktop, Service, and Mobile? ⭐

Answer:

• Power BI Desktop: Free authoring tool for creating reports and data models (ETL, relationships, DAX).
• Power BI Service: Cloud-based SaaS for sharing, collaborating, scheduling refresh, and creating dashboards/apps.
• Power BI Mobile: Apps for iOS/Android to view reports/dashboards with offline access and QR scanning.

Real-time scenario:
You build a Sales report in Desktop, publish to Service, set up auto-refresh every hour, and share a dashboard with field sales reps who use Mobile app to see real-time numbers.

Q2: What are the building blocks of Power BI?

Answer:

1. Visualizations – Charts, maps, slicers, etc.
2. Datasets – Source data (Excel, SQL, SharePoint, etc.)
3. Reports – One or more pages of visuals.
4. Dashboards – Single-page canvas with pinned visuals from multiple reports.
5. Tiles – Individual visuals pinned to a dashboard.

Scenario:
*You have a report with 5 pages (Sales, Profit, Inventory). You pin one KPI card from Sales page + one chart from Inventory page to a Dashboard for executives.*

2. Data Modeling & Relationships (High Priority) ⭐

Q3: Explain Star Schema vs Snowflake Schema. Which is better for Power BI?

Answer:

• Star Schema: One fact table (sales) connected directly to multiple dimension tables (product, customer, date). Fast, simple.
• Snowflake Schema: Dimension tables normalized (e.g., product → subcategory → category). More complex, slower in Power BI.

Power BI preference: Star Schema. Snowflake can cause performance issues and ambiguous relationships.

Real-time scenario:
You have a product table with category name stored directly. That’s star. If category is separate table linked via subcategory, that’s snowflake – avoid by merging in Power Query.

Q4: What are the types of relationships in Power BI?

Answer:

1. One-to-many (1:*) – Most common. One product has many sales.
2. Many-to-one (*:1) – Reverse of above.
3. One-to-one (1:1) – Rare, usually data quality issue.
4. Many-to-many (:) – Complex. Requires bridge table or DAX.

Scenario:
You have Sales (multiple rows per product) and Product (one row per product) → 1: from Product to Sales.
If you have Student and Course tables with Enrollment bridge → : via bridge table.*

3. Power Query (M Language) – Real Scenarios ⭐

Q5: How do you handle missing or null values in Power Query?

Answer: Options:

• Remove rows (if few).
• Replace with mean/median (for numeric).
• Replace with “Unknown” (for text).
• Fill down/up (for time series).
• Use conditional column logic.

Real-time scenario:
Customer feedback table has 30% null in “Region”. You check Sales table for mapping by city. Remaining nulls set to “Unknown” and create a “Region (Grouped)” column.

Q6: How to merge vs append queries? When to use each?

Answer:

• Merge: Join tables horizontally (add columns from another table) – like SQL JOIN.
• Append: Stack tables vertically (add rows) – like UNION ALL.

Scenario:
You have Sales2023 and Sales2024 with same columns → Append.
You have Sales and Product tables → Merge on ProductID to add ProductName.

4. DAX (Highest Priority – Expect Multiple Questions) ⭐⭐⭐

Q7: Explain CALCULATE() with a real example.

Answer:
CALCULATE( <expression>, <filter1>, <filter2> ... )
Changes context of calculation.

Real scenario:
*Total Sales = SUM(Sales[Amount])
Sales for 2024 only = CALCULATE( [Total Sales], YEAR(Sales[Date]) = 2024 )
Sales in USA = CALCULATE( [Total Sales], Customer[Country] = “USA” )*

Tricky:
CALCULATE( SUM(Sales[Amount]), ALL(Product[Category]) ) – removes filter from Category.

Q8: What is the difference between SUM and SUMX? ⭐

Answer:

• SUM(Column) – simple aggregate over column.
• SUMX(Table, Expression) – row-by-row evaluation, then sum.

Scenario:
You need total revenue = Quantity * Price per row.
SUMX(Sales, Sales[Qty] * Sales[Price]) is correct.
SUM(Sales[Qty] * Sales[Price]) is invalid (SUM expects single column).

Q9: How to calculate Year-to-Date (YTD), Month-over-Month (MoM)?

Answer:

• YTD: TOTALYTD( SUM(Sales[Amount]), 'Date'[Date] )
• MoM: Use PREVIOUSMONTH or DATEADD.

Real scenario:
Sales MoM % change =
VAR CurrentMonth = SUM(Sales[Amount])
VAR PrevMonth = CALCULATE( SUM(Sales[Amount]), PREVIOUSMONTH(‘Date'[Date]) )
RETURN DIVIDE( CurrentMonth – PrevMonth, PrevMonth )

Q10: What is the difference between USERELATIONSHIP and CROSSFILTER?

Answer:

• USERELATIONSHIP – Activate an inactive relationship for a measure.
• CROSSFILTER – Change filter direction (both ways) in a calculation.

Scenario:
Sales table has OrderDate and ShipDate. Two relationships to Date table (inactive). To calculate sales by ship date:
CALCULATE( SUM(Sales[Amount]), USERELATIONSHIP(Sales[ShipDate], 'Date'[Date]) )

5. Performance Optimization (Critical for Real Projects) ⭐

Q11: How do you improve slow Power BI reports?

Answer (Top 5):

1. Star schema – avoid snowflake.
2. Reduce data – filter rows, remove unnecessary columns in Power Query.
3. Use Summarize/Group By instead of detailed rows.
4. Avoid calculated columns if possible (use measures).
5. Disable auto date/time (File → Options → Data Load).

Real scenario:
Sales table with 50M rows. You import only last 2 years, remove 20 unused columns, aggregate to daily level, and use DirectQuery for current day only.

Q12: When to use Import vs DirectQuery?

Answer:

Scenario:
Real-time stock inventory – DirectQuery.
Historical sales analysis – Import.

Hybrid: Composite model (some import, some DirectQuery).

6. Advanced Real-Time Scenarios (Expect These) ⭐

Q13: How to implement Row-Level Security (RLS) for regional managers?

Answer:

1. In Desktop: Modeling → Manage Roles.
2. Define DAX filter, e.g., Region[RegionName] = USERNAME().
3. In Service: Assign users to role.

Scenario:
You have Sales table with Region column. Create role “RegionManager” with filter:
Sales[Region] = LOOKUPVALUE(UserRegion[Region], UserRegion[Email], USERPRINCIPALNAME())

Q14: Your report has incorrect totals when using a filter. Why?

Answer:
This happens when a measure does not respect filter context in grand totals, typically due to:

• Using ALL() without KEEPFILTERS.
• Incorrect CALCULATE logic.

Fix example:
Bad: Sales % = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Product)) )
Better: Sales % = DIVIDE( SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALLSELECTED(Product)) )

Q15: How do you handle slowly changing dimensions (Type 2)?

Answer:
Type 2 SCD keeps history with effective date and current flag.
In Power BI:

• Load SCD table as is.
• Use a role-playing date table for effective date.
• For “current” view, filter IsCurrent = TRUE.

Scenario:
*Employee_History table has EmpID, Manager, StartDate, EndDate, IsCurrent. To see current manager for each sales record, merge/relationship on EmpID and filter IsCurrent=1 in DAX or Power Query.*

7. Visualization & Dashboard Design

Q16: What is the difference between a report and a dashboard?

Answer:

Q17: How to create a parameter to switch measures dynamically?

Answer:
Use Field Parameter (introduced 2022) or Calculation Group.

Real scenario:
User selects from dropdown: Sales, Profit, Quantity. All charts update.
Create Field Parameter with those measures, drag to axis/value.

Legacy way (DAX):
Selected Measure = SWITCH( SELECTEDVALUE(Parameter[Measure]), "Sales", [Total Sales], "Profit", [Total Profit] )

8. Power BI Service & Administration

Q18: Explain incremental refresh. When would you use it?

Answer:
Only refreshes new/changed data instead of entire dataset.
Requires: Premium or Premium Per User (PPU), date column.

Scenario:
Sales table with 5 years history. Set incremental refresh: refresh last 30 days (rolling), keep 2 years history. First load full, then daily only new data.

Q19: How to set up email subscriptions based on data changes?

Answer:

1. Create a Subscription in Service for a report page.
2. Use Data-driven alerts (Premium) – e.g., if sales < threshold, email.

Scenario:
Exec wants daily 9 AM PDF of “Yesterday Sales” report. Set subscription.
If inventory < 100 units, trigger alert to procurement team.

9. Scenario-Based Problem Solving (Most Likely to Be Asked) ⭐⭐⭐

Q20: You are given 10 different Excel files (sales, returns, targets) with inconsistent product codes. Build a single report showing actual vs target. Walk me through.

Answer (Step-by-Step):

1. Power Query:
   • Create a Product Master by merging unique codes from all files (fuzzy match if needed).
   • Standardize date columns.
2. Model:
   • Fact tables: Sales, Returns, Targets.
   • Dim tables: Product, Date.
3. Measures:
   • Net Sales = SUM(Sales[Amount]) - SUM(Returns[Amount])
   • Target vs Actual = DIVIDE( [Net Sales], SUM(Targets[TargetAmount]) )
4. Visuals:
   • Matrix: Product hierarchy vs Month.
   • Waterfall: Monthly variance.

Q21: Your report on Service refreshes successfully but shows wrong data. How do you debug?

Answer:

1. Check Refresh history in dataset settings (errors/timeout).
2. Compare Service vs Desktop numbers – export to Excel from both.
3. Check Gateway if on-prem source.
4. Verify RLS – someone might have limited access.
5. Look for implicit measures or auto date/time behavior differences.
6. Check time zone – data may be UTC in service, local in desktop.

Q22: How would you compare two periods (e.g., This Month vs Last Month) with same day-of-week alignment?

Answer:
Use a custom date table with WeekdayIndex and WeekOffset.

DAX:

text

Sales LM Same Weekday = 
VAR CurrentDate = MAX('Date'[Date])
VAR CurrentWeekday = WEEKDAY(CurrentDate)
VAR LastMonthSameWeekday = 
    DATEADD( CurrentDate, -28, DAY )  // approximate
// Better: Use a calendar table with week-of-year + weekday
RETURN
CALCULATE( [Sales], 
    FILTER( ALL('Date'), 
        'Date'[WeekOfYear] = EARLIER('Date'[WeekOfYear]) - 4 
        && 'Date'[Weekday] = CurrentWeekday
    )
)

Better approach: Create a Week Index column in date table and use SAMEPERIODLASTYEAR for year-over-year.

10. Quick Fire High-Priority Questions

Final Tips for Interview:

• Always start with: “First, I understand the business requirement.”
• For DAX questions: Write the formula + explain row/filter context.
• For performance: Mention VertiPaq, columnar storage, and cardinality.
• For real scenarios: Draw a small star schema on paper.

Power BI is Microsoft’s leading business intelligence and data visualization platform. Interview questions typically cover fundamentals, data modeling, Power Query/DAX, visualizations, security, performance, deployment, and real-world troubleshooting. Below is a structured compilation of common and advanced questions, emphasizing real-time scenarios drawn from enterprise use cases (sales analytics, finance, operations, etc.).

1. Basics and Architecture

Q: What is Power BI? What are its main components? A: Power BI is a self-service BI tool for connecting to data sources, transforming data, modeling, visualizing, and sharing insights. Key components:

• Power BI Desktop: For report authoring, modeling, and Power Query/DAX.
• Power BI Service (PowerBI.com): Cloud platform for sharing, collaboration, dashboards, and scheduled refreshes.
• Power BI Mobile: Apps for iOS/Android.
• Power BI Report Server: On-premises alternative.
• Gateways: Personal/On-premises for refreshing on-prem data. Real-time scenario: In a retail company, you use Desktop to build sales reports from SQL + Excel, publish to Service for executive dashboards, and set up a gateway for daily on-prem ERP refreshes.

Q: Difference between Power BI, Tableau, and Excel? A: Power BI excels in Microsoft ecosystem integration, cost-effectiveness, and DAX modeling. Tableau offers superior advanced visualizations but is pricier. Excel is great for ad-hoc but lacks scalability for large datasets or sharing. Power BI bridges self-service and enterprise needs better for most organizations.

Q: Explain Import, DirectQuery, and Live Connection modes. A:

• Import: Data loaded into in-memory model (fast, but size limits; good for <1-10M rows).
• DirectQuery: Queries source live (no size limit, but slower visuals; ideal for huge transactional data).
• Live Connection: To SSAS/Azure Analysis Services (leverages existing models). Scenario: For near real-time sales dashboards with millions of rows, switch to DirectQuery on SQL Server, but optimize with aggregates to avoid timeouts.

2. Data Connectivity and Power Query (M Language)

Q: What data sources does Power BI support? How do you handle multiple sources? A: Files (Excel, CSV), Databases (SQL, Oracle, Snowflake), Cloud (Azure, Google Analytics), Web APIs, etc. Use Power Query for ETL: combine via Append/Merge, handle transformations. Scenario: Merge sales from SQL (fact table) with product master from Excel and customer data from CRM API. Use fuzzy matching for inconsistent keys and query folding for performance.

Q: Explain Query Folding and why it matters. A: Query Folding pushes transformations back to the source (e.g., SQL) instead of loading raw data. It reduces memory use and improves speed. Disable only when necessary (e.g., custom M functions). Scenario: In a large daily ETL, folding filters early in Power Query prevents loading terabytes into Desktop.

3. Data Modeling

Q: What is a Star Schema vs. Snowflake Schema? Best practices? A: Star: Central fact table (measures like SalesAmount) connected to denormalized dimension tables (Product, Date, Customer). Optimal for Power BI performance. Snowflake: Normalized dimensions (more tables, joins). Use Star for most cases. Best practices: Use surrogate integer keys, single-direction relationships (avoid bi-directional unless needed), hide unused columns, mark Date table.

Real-time Scenario: Multi-region sales data modeling. You have separate Sales, Products, Customers, Regions tables. Design a Star Schema with Sales as fact. Create relationships on keys. Use DAX for measures like Total Sales = SUM(Sales[Amount]). This enables fast cross-filtering and avoids circular dependencies.

Q: Handling many-to-many relationships? A: Create a bridging table or use DAX (e.g., TREATAS). Prefer resolving at source or with inactive relationships + USERELATIONSHIP. Scenario: Employees linked to multiple projects. Bridge table prevents ambiguity in reporting.

4. DAX (Critical for Interviews)

Q: Difference between Calculated Columns and Measures? Row vs. Filter Context? A: Calculated Columns: Computed row-by-row at refresh (static, uses more memory). Measures: Dynamic, evaluated at query time (preferred for aggregations). Row Context: Current row (e.g., in calculated column). Filter Context: Filters from slicers/visuals/relationships (modified by CALCULATE).

Key DAX Functions & Scenarios:

• CALCULATE: Modifies filter context. Scenario: Sales in specific region = CALCULATE(SUM(Sales[Amount]), Sales[Region] = “East”).
• Time Intelligence: TOTALYTD, SAMEPERIODLASTYEAR, DATESMTD. Scenario: YTD Sales = TOTALYTD(SUM(Sales[Amount]), ‘Date'[Date]). Ensure marked Date table.
• Dynamic Measures: Use SWITCH + SELECTEDVALUE for user-selectable KPIs (Sales vs. Profit).
• DIVIDE: Safe division (handles /0). Profit Margin = DIVIDE([Profit], [Revenue], 0).
• SUMX vs. SUM: SUMX iterates row-by-row (e.g., weighted averages).

Advanced Scenario: Running Total / YoY Growth. Running Total = CALCULATE(SUM(Sales[Amount]), FILTER(ALL(‘Date’), ‘Date'[Date] <= MAX(‘Date'[Date]))). YoY = DIVIDE([Sales] – CALCULATE([Sales], SAMEPERIODLASTYEAR(‘Date'[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR(‘Date'[Date]))).

5. Visualizations, Reports, and Dashboards

Q: Types of filters and how to control interactions? A: Visual/Page/Report-level. Edit interactions to make one slicer affect specific visuals only. Scenario: Executive dashboard with global date slicer but department-specific filters via bookmarks or drill-through.

Q: Best practices for performant reports? A: Limit visuals per page, use aggregations, avoid heavy custom visuals, apply filters early, use tooltips.

6. Security and Sharing

Q: Explain Row-Level Security (RLS). A: DAX-based dynamic filtering. Create roles in Modeling > Manage Roles (e.g., [Region] = USERPRINCIPALNAME()). Assign in Service. Test with “View as”. Scenario: Sales managers see only their region’s data. Use USERPRINCIPALNAME() or a mapping table for email-based security.

Q: Data Gateway? A: Bridges on-prem data to cloud. Personal for dev; Enterprise (On-premises) for production with high availability.

7. Performance Optimization and Troubleshooting (High-Impact Scenarios)

Real-time Scenario: Slow report with large dataset. Steps:

1. Performance Analyzer (in Desktop) to identify slow visuals/DAX.
2. Optimize model: Remove columns, use integers, star schema, incremental refresh.
3. DAX: Avoid calculated columns, use variables, SUMMARIZE.
4. Query folding, reduce visuals, aggregations tables.
5. For refreshes: Incremental refresh (RangeStart/RangeEnd parameters; keep 2-5 years history, refresh last 7-30 days).

Incremental Refresh Scenario: Large daily-updating transaction table. Partition by date in Power Query, enable policy in Service. Dramatically cuts refresh time.

Q: Handling real-time / near real-time data? A: DirectQuery, Streaming Datasets (Push API for IoT/dashboards), or Azure integrations. Schedule refreshes up to 48/day (Premium).

8. Advanced / Enterprise Topics

• Composite Models: Mix Import + DirectQuery.
• Paginated Reports: For printable, pixel-perfect reports (Report Builder).
• AI Visuals: Key influencers, decomposition tree.
• Deployment: Workspaces, Apps, Content Packs, Power BI Premium for large scale. Scenario: Migrate legacy SSRS reports to Power BI + paginated for finance close processes.

Common Project Experience Question: Describe your end-to-end Power BI project. Answer Structure: Requirements gathering → Data sources/ETL (Power Query) → Modeling (Star) → DAX/KPIs → Visuals/Dashboards → Security/RLS → Publish/Refresh/Gateway → Monitoring/Optimization. Mention business impact (e.g., reduced reporting time by 70%).

Preparation Tips

• Practice with real datasets (AdventureWorks, public sales data).
• Master DAX with DAX Studio for query analysis.
• Know licensing (Free/Pro/Premium) implications.
• Be ready for scenario-based live demos: “Build a dynamic dashboard for this data.”
• Tools: Performance Analyzer, DAX Studio, Tabular Editor.