Top 15 Power BI Interview Questions and Core Architectural Concepts

Modern Power BI interviews evaluate far more than the ability to drag and drop visual charts onto a canvas. Enterprise teams require business intelligence engineers who understand columnar database optimization, DAX calculation engines, data modeling, enterprise security, and scalable refresh architectures. The following comprehensive study guide breaks down the core concepts, patterns, and mechanisms essential for both technical interviews and real-world analytics deployments.

1. Data Modeling Fundamentals: Measures vs. Calculated Columns

Both calculated columns and measures enable custom computations in Power BI, but their internal execution engines, memory footprints, and evaluation contexts differ fundamentally.

Calculated Columns

• Storage & Evaluation: Evaluated during data refresh on a row-by-row basis (Row Context) and physically stored in the database model using the VertiPaq compression engine.
• Resource Footprint: Increases file size (.pbix) and consumes persistent system RAM.
• Primary Use Case: When the calculation output must serve as a slicer, filter, or row/column axis in a matrix or visual.

// Example: Stored permanently in every row of the Sales table
Sales[Total Sales] = Sales[Price] * Sales[Quantity]

Measures

• Storage & Evaluation: Computations defined via Data Analysis Expressions (DAX) that execute dynamically when an end user interacts with a visual (Filter Context).
• Resource Footprint: Zero disk storage overhead; calculated in transient memory during query execution and discarded after rendering.
• Primary Use Case: Numerical aggregations, percentages, ratios, and dynamic Key Performance Indicators (KPIs).

// Example: Evaluated dynamically based on active report filters
Total Revenue = SUMX(Sales, Sales[Price] * Sales[Quantity])

Conceptual Architecture Comparison

2. DAX Engine & Iterator Mechanics: SUM vs. SUMX

DAX (Data Analysis Expressions) is the native functional expression language used across Power BI, Excel Power Pivot, and SQL Server Analysis Services (SSAS). DAX empowers analysts to compute dynamic running totals, time intelligence metrics, ranking, and complex conditional ratios not natively stored in source tables.

Aggregation Functions: SUM

SUM is an aggregation function that operates across a single numeric column. It sums all active values within the current filter context without scanning additional columns or evaluating row-level conditional operations.

Total Cost = SUM(Sales[Cost])

Iterator Functions: SUMX

SUMX is an iterator function with an X suffix. Iterators create a temporary Row Context over a specified table, loop through each row, compute a row-level expression, and aggregate the results into a single scalar value.

Total Sales = 
SUMX(
    Sales, 
    Sales[Price] * Sales[Quantity]
)

Why Not Just Add a Calculated Column and Run SUM?

Creating a calculated column to multiply Price * Quantity and then running SUM forces the VertiPaq engine to persist an entire column of integers or decimals, bloats memory, and degrades model performance. SUMX computes the exact same result in-flight, preserving system resources.

3. Data Interaction: Filters, Slicers, and Groups

Reports rely on three core constructs to organize, isolate, and navigate analytical dimensions.

Filters (Backend / Developer Rules)
   |
Slicers (Interactive UI Canvas Controls)
   |
Groups (Structural Categorization / Bucketing)

• Filters: Configured primarily in the Power BI Desktop filter pane. Filters operate invisibly behind the scenes and can be scoped at three distinct hierarchy levels:
  1. Visual Level: Constrains only the selected chart or matrix.
  2. Page Level: Constrains all visuals across the current report tab.
  3. Report Level: Constrains all queries throughout every page in the document.
• Slicers: Interactive visual objects placed directly on the report canvas. Slicers allow report consumers to alter the active filter context through UI controls such as drop-downs, date pickers, or radio buttons.
• Groups: Design-time or model-level groupings that combine numerous granular categorical attributes into higher-level business segments (e.g., grouping 50 individual sub-categories into Electronics, Clothing, and Accessories) without modifying source database schemas.

4. Handling Missing, Null, and Blank Values

Real-world datasets contain missing values, partial entries, and nulls. Power BI provides two primary layers to clean and normalize these fields: Power Query (ETL layer) and DAX (data modeling layer).

In Power Query (M Language)

Cleaning data before it enters the analytical model is optimal for performance:

• Replace Values: Substitute null records with default values (such as 0 for numeric values or "Unknown" for categorical fields).
• Remove Rows: Filter out records where primary identity keys (e.g., OrderID) are null.
• Fill Down / Fill Up: Propagate values across consecutive null segments for denormalized spreadsheets.

In DAX Expressions

When calculations must gracefully resolve missing data dynamically at runtime, conditional expressions maintain calculation integrity:

// Fallback substitution with COALESCE (returns first non-blank argument)
Clean Unit Price = COALESCE(Sales[Price], 0)

// Conditional blank evaluation using IF and ISBLANK
Adjusted Price = 
IF(
    ISBLANK(Sales[Price]), 
    0, 
    Sales[Price]
)

5. Visualizing Business Health: Key Performance Indicators (KPIs)

A Key Performance Indicator (KPI) is a specialized visual metric designed to communicate progress against a quantifiable business goal.

Building a Production KPI Visual

1. Base Measure: Define the actual current progress metric via DAX (e.g., Actual Sales = SUM(Sales[Revenue])).
2. Target Goal: Define the benchmark metric (e.g., a static budget target or a dynamic DAX budget measure: Sales Target = SUM(Budgets[Target])).
3. Trend Axis / Context: Map a continuous temporal dimension (e.g., Calendar[Month]) to establish historical trajectory.
4. Conditional Formatting Thresholds: The KPI engine automatically compares the current value to the target. If actual values fall below target, it applies visual alerts (e.g., red indicators, downward arrows); if targets are met or exceeded, it renders positive alerts (e.g., green indicators, upward checks).

6. Enterprise Data Governance: Row-Level Security (RLS)

Row-Level Security (RLS) restricts data access for given users based on authentication filters. Instead of creating distinct report files for each department or territory, a single master report uses RLS to display only authorized rows to each logged-in user.

RLS Implementation Architecture

Implementation Steps

1. Define Security Roles: Inside Power BI Desktop, navigate to Modeling > Manage Roles.
2. Apply DAX Predicate: Write a boolean filter expression against the target dimension table:
   DAX

   Copy

   [Region] = "North"
   

   Note: For dynamic RLS across large enterprises, avoid hardcoded values. Instead, leverage user identity functions:

   DAX

   Copy

   [Email] = USERPRINCIPALNAME()
   

3. Validate in Desktop: Use the View As tool to simulate the report through specific role assignments.
4. Assign Users in Service: Publish the report to the Power BI Service, open the dataset settings, select Security, and assign Active Directory security groups or email addresses to the defined roles.

7. Ingestion Architecture: Integrating Multiple Data Sources

Modern business intelligence environments aggregate fragmented data sources into a unified dimensional model. Power BI allows cross-source relationships between on-premises databases, cloud applications, and flat files.

Ingestion and Unification Workflow

1. Get Data: Select native connectors (e.g., SQL Server, Oracle, Azure Data Lake, Google BigQuery, Excel).
2. Normalize in Power Query: Ensure common keys have matching data types across sources (e.g., normalizing CustomerID as Int64 in both SQL and CSV queries).
3. Model Star Schema: In the Model View, construct clear 1-to-Many (1:*) relationships between single Dimension tables and transactional Fact tables.

8. Interactive Report UX: Bookmarks and Dynamic Views

Bookmarks capture the exact configuration of a report page—including filter context, visual visibility, slicer positions, and sorting states.

Core Applications of Bookmarks

• View Toggling: Switching between high-level executive cards and granular matrix tables using overlaid buttons.
• Filter Reset Switches: Creating a one-click button configured to clear all visual selections and reset slicers to their default state.
• Step-by-Step Storytelling: Guiding stakeholders through an analytical narrative by toggling visibility across annotations and chart highlights.

// Complementing bookmarks with dynamic measure switching using SWITCH
Selected Metric = 
SWITCH(
    SELECTEDVALUE(MetricSelection[MetricName]),
    "Total Sales", [Total Revenue],
    "Total Profit", [Total Profit],
    "Units Sold", SUM(Sales[Quantity]),
    [Total Revenue]
)

9. Performance & Ingestion: Scheduled Refresh vs. Incremental Refresh

Enterprise databases frequently store millions of historical records. Refreshing an entire dataset on every cycle causes network bottlenecks, source system contention, and prolonged refresh times.

Real-World Analogy

Scheduled Refresh is like re-reading an entire 1,000-page book from page one every time a new paragraph is published. Incremental Refresh is like reading only the newest page while keeping the previous 999 pages safely stored on your bookshelf.

Architectural Comparison

10. Data Warehouse Integration: Handling Slowly Changing Dimensions (SCD)

A Slowly Changing Dimension (SCD) occurs when attributes in a dimension table change gradually over time (e.g., a customer changes states, or an employee moves between departments).

SCD Type 1 vs. SCD Type 2

11. Techniques for Making Reports Dynamic

Dynamic reports allow business users to change views, metrics, and data scopes without modifying the underlying model.

• Slicers & Cross-Filtering: Selecting a segment inside a bar chart dynamically recalculates the contextual filter for surrounding visuals.
• Drill-Through Pages: Navigating from a high-level summary visual into a dedicated, context-filtered sub-page focused on a specific entity (e.g., right-clicking a customer to see their order history).
• Parameter-Based Metrics: Utilizing What-If Parameters or disconnected tables to alter variable inputs (such as growth rates or currency selectors) dynamically.

12. Publishing and Enterprise Deployment Workflows

Moving a report from a local development environment into production follows an established deployment path to the Power BI Service.

13. Freezing and Controlling Report Interactivity

Scenarios often arise where developers must freeze visual outputs or disable dynamic updates—such as preserving a month-end audit snapshot or isolating a chart from canvas slicers.

Key Stabilization Techniques

• Edit Interactions: By default, clicking a visual filters all other visuals on the canvas. Developers can select a visual, navigate to Format > Edit Interactions, and set specific destination charts to None. This isolates them from slicer selections.
• Disabling Scheduled Refreshes: In the Power BI Service, open the dataset settings and toggle off Scheduled Refresh to freeze the data model in its current state.
• Storage Mode Selection (Import vs. DirectQuery): DirectQuery fetches live updates directly from the underlying database upon every visual interaction. Using Import Mode caches data in memory, preventing changes until an intentional refresh is initiated.

14. Extending Visual Capabilities: Custom Visuals

When native visual libraries (bar charts, line graphs, matrices) cannot convey specific domain requirements, developers can introduce Custom Visuals.

Native Visuals (Bar, Line, Pie, Matrix)
       vs.
Custom Visuals (Sankey, Word Cloud, Bullet Charts, Chords)

When to Deploy Custom Visuals

• Unstructured / Text Data: Word Clouds present keyword densities across customer surveys, open feedback logs, or support tickets.
• Process & Resource Flows: Sankey diagrams visualize complex multi-stage flows, such as customer conversion funnels or supply chain routes.
• Specialized Executive Metrics: Bullet charts visualize dense performance targets and qualitative bands within compact spaces, outperforming standard gauges.
• Sourcing: Available via Microsoft AppSource directly within Power BI Desktop, or developed internally using the custom visual SDK (TypeScript/D3.js).

Summary and Key Takeaways

• Calculated Columns vs. Measures: Calculated columns evaluate during data load and consume physical RAM via VertiPaq. Measures evaluate on-demand during runtime query execution, saving memory and processing power.
• DAX Iterator Logic: SUM calculates single-column aggregates; SUMX initializes an explicit row context to compute multi-column mathematical operations before summing results.
• Data Cleansing Stratification: Perform static data cleanup (such as null handling) upstream in Power Query to reduce model bloat; utilize DAX functions like COALESCE for dynamic runtime logic.
• Row-Level Security (RLS): Enforce data governance by configuring DAX filter expressions within roles in Desktop, and assigning Azure Active Directory users in Power BI Service.
• Refresh Optimization: Replace full scheduled reloads with partitioned Incremental Refreshes for large datasets to reduce engine load, network bandwidth, and refresh runtimes.
• Dimension Modeling: Handle data mutations using SCD Type 1 (overwriting when history is not needed) or SCD Type 2 (versioning rows with date validity ranges to preserve historical context).
• Report Control: Isolate visual interactivity via Edit Interactions, and control model dynamism by choosing between Import Mode and DirectQuery storage engines.