Power BI Interview Questions — Comprehensive Guide

Prepared: Comprehensive set of conceptual, technical, and scenario-based interview questions (with
concise answers for concept items and suggested approaches for scenarios).

Table of Contents
1. Basics & What■is Questions
2. Data Modelling & Schema Design
3. Power Query / M
4. DAX — Basics
5. DAX — Advanced & Performance
6. Visualizations & Report Design
7. Power BI Service, Deployment & Administration
8. Security (RLS, Row Level Security) & Governance
9. Performance Tuning & Optimization
10. Scenario-Based Interview Questions (with suggested approaches)
11. Practical / Hands-on Tasks and Sample Tables
Appendix: Quick cheat-sheet & common commands
1. Basics & What■is Questions
Q: What is Power BI?
A: Power BI is a Microsoft business analytics service that provides interactive visualizations and BI
capabilities with a simple interface for end users to create reports and dashboards. It consists of Desktop,
Service (cloud), Mobile apps, and Report Server.

Q: What are the main components of Power BI?

A: Power BI Desktop, Power BI Service, Power BI Mobile, Power BI Report Server, Power BI Gateway,
and Power BI Report Builder (for paginated reports).

Q: What is PBIX?
A: PBIX is the file format for Power BI Desktop reports (contains model, visuals, queries, and metadata).

Q: Difference between Power BI Desktop and Power BI Service?

A: Desktop is the authoring tool to build reports locally. Service is the cloud platform to publish, share,
schedule refreshes, and manage workspaces.

Q: What is a Dataset in Power BI?

A: A Dataset is a collection of data imported or connected to Power BI, including tables, relationships,
measures, and metadata used to create reports.

Q: What is a Report vs Dashboard?

A: A Report is a multi-page interactive collection of visuals built from a dataset. A Dashboard is a
single-page (canvas) that pins visuals from reports and provides a consolidated view.

Q: What is Power Query?

A: Power Query (M) is the ETL layer in Power BI Desktop used to connect, clean, transform and shape
data before loading into the model.

Q: What is DAX?
A: Data Analysis Expressions — a formula language used to define measures, calculated columns and
tables in Power BI and Analysis Services.

Q: What are measures and calculated columns?

A: Measures are dynamic calculations evaluated in query context (filter context). Calculated columns are
computed during data refresh and stored in the model as values for each row.

Q: Import vs DirectQuery vs Live Connection?

A: Import loads data into the in-memory VertiPaq engine for fast queries. DirectQuery sends queries to the
source at runtime (no import). Live connection connects to an Analysis Services model or Power BI
dataset (metadata-only connection).

Q: What is VertiPaq?
A: VertiPaq is Power BI's in-memory columnar storage engine that compresses and stores data for
high-performance analytics.

Q: What is an aggregation table?

A: Aggregation tables store pre-aggregated data (e.g., monthly instead of daily) to improve query
performance for large models.
2. Data Modelling & Schema Design
Q: What is star schema and why prefer it?
A: Star schema organizes data into fact and dimension tables. It simplifies relationships, improves query
performance with VertiPaq, and helps DAX time-intelligence.

Q: What is a snowflake schema? When to avoid it?

A: Snowflake normalizes dimension tables into multiple related tables. Avoid for reporting models in Power
BI because extra joins can slow performance; prefer denormalized star schema.

Q: How do you handle many-to-many relationships?

A: Use bridge tables, composite models, or modeling features like relationship with cross-filter direction
and DAX functions (e.g., TREATAS). Power BI also supports many-to-many relationships with
single-direction filtering in modern versions.

Q: What is relationship cardinality?

A: Cardinality defines how rows in two tables relate: one-to-one, one-to-many, many-to-one, or
many-to-many. Choose correct cardinality for accurate filtering.

Q: Explain cross-filter direction.

A: Cross-filtering determines how filters flow across relationships: Single (one direction) or Both
(bi-directional). Bi-directional can simplify calculations but may cause ambiguity and performance issues.

Q: What is role of surrogate keys?

A: Surrogate keys are artificial integer keys used for joins to improve performance and stability versus
natural keys that may change.

Q: What is normalization vs denormalization in BI models?

A: Normalization reduces redundancy (good for OLTP), denormalization duplicates data to simplify
querying and is usually better for BI star schemas.
3. Power Query / M
Q: What is Power Query M language?
A: M is the functional language that underlies Power Query, used to define ETL steps. It's case-sensitive
and good for row-level transformations and query folding.

Q: What is query folding?

A: Query folding is pushing transformation steps back to the data source (e.g., SQL server) so heavy work
runs on server side, improving performance.

Q: How to check if query folding is happening?

A: Right-click a query step in Power Query and use 'View Native Query' (if enabled) to see the folded SQL;
some steps break folding.

Q: When to use 'Merge' vs 'Append' in Power Query?

A: Merge = join tables horizontally. Append = stack tables (union) vertically.

Q: How to handle incremental refresh in Power BI?

A: Define range parameters (RangeStart/RangeEnd), configure incremental refresh policy in Power BI
Desktop, and publish to workspace with Premium or PPU for true incremental refresh.
4. DAX — Basics
Q: What is filter context and row context?
A: Row context applies per row in a table (iterators or calculated columns). Filter context is the set of filters
applied by visuals, slicers, or CALCULATE; measures evaluate in filter context.

Q: What does CALCULATE do?

A: CALCULATE modifies filter context of an expression by applying filters, removing filters, or changing
context, and then evaluates the expression.

Q: Difference between SUM and SUMX?

A: SUM aggregates a single column. SUMX is an iterator that evaluates an expression row-by-row and
then sums results — useful for row-level calculations.

Q: What are iterator functions in DAX?

A: Functions that iterate over table rows such as SUMX, AVERAGEX, FILTER, ADDCOLUMNS — they
create row context for their expression.

Q: Explain context transition.

A: When a row context is converted into an equivalent filter context (e.g., when CALCULATE is called
inside a row context), it's called context transition.
5. DAX — Advanced & Performance
Q: How to optimize slow DAX measures?
A: Use variables to avoid repeated work, reduce use of iterators on large tables, prefer measure over
calculated columns where possible, minimize use of DISTINCTCOUNT on large high-cardinality columns,
and use SUMMARIZECOLUMNS/ADDCOLUMNS carefully.

Q: What is the difference between ALL and ALLEXCEPT?

A: ALL removes filters from specified columns/tables. ALLEXCEPT removes all filters except those
specified columns.

Q: How to write time-intelligence measures (YTD, MTD)?

A: Use TIMEINTELLIGENCE functions like TOTALYTD, SAMEPERIODLASTYEAR, or build with
CALCULATE + DATEFILTER using a proper Date table marked as Date.

Q: What is USERELATIONSHIP used for?

A: USERELATIONSHIP activates an inactive relationship inside CALCULATE for the duration of the
expression.

Q: When to use SUMMARIZE vs SUMMARIZECOLUMNS?

A: SUMMARIZECOLUMNS is optimized for query performance and respects current filters; SUMMARIZE
had quirks historically. Prefer SUMMARIZECOLUMNS for aggregation queries when possible.
6. Visualizations & Report Design
Q: How to design an effective Power BI report?
A: Use clear goals, audience-focused KPIs, consistent formatting, limited visuals per page, responsive
layout, proper use of bookmarks and tooltips, accessible colors, and ensure filters/slicers are intuitive.

Q: What are bookmarks and when to use?

A: Bookmarks capture the current state of a report page (filters, slicers, visibility) and are used for
storytelling, navigation, or toggle visuals.

Q: Difference between drillthrough and tooltip pages?

A: Drillthrough navigates to a target page with context, used to show details. Tooltip pages display small
popup pages for hover info.

Q: How to use custom visuals safely?

A: Use visuals from AppSource or vetted sources, check performance impact, licensing, and ensure they
meet security and governance policies.
7. Power BI Service, Deployment & Administration
Q: What is a workspace?
A: Container in Power BI Service for datasets, reports, dashboards, and workbooks. Workspaces can be
personal (My Workspace) or app workspaces that publish apps.

Q: How to schedule data refresh?

A: In Service, configure dataset scheduled refresh with credentials and gateway (if on-prem), set
frequency and refresh times. Import mode supports scheduled refresh; DirectQuery uses live queries.

Q: What is a gateway and types?

A: On-premises data gateway (personal or enterprise) allows cloud service to access on-prem data.
Personal gateway is for one user; enterprise supports multiple users, live connections and refreshes.

Q: What are Apps and how to use them?

A: Apps are packaged content from a workspace that can be distributed to users with controlled access;
they simplify sharing curated reports and dashboards.
8. Security (RLS) & Governance
Q: What is Row Level Security (RLS)?
A: RLS restricts data access for users at row-level by defining roles and filter expressions (static or
dynamic). Dynamic RLS typically uses USERPRINCIPALNAME() or USERNAME() in DAX.

Q: How to implement dynamic RLS?

A: Create a mapping table of users to values, create a role with DAX filter like [UserEmail] =
USERPRINCIPALNAME(), publish, and assign users to the role in Power BI Service.

Q: What is workspace access vs dataset permissions?

A: Workspace roles (Admin, Member, Contributor, Viewer) control object-level access. Dataset
permissions allow specific actions like build permission for reuse of datasets.

Q: What governance considerations for Power BI?

A: Naming standards, dataset certification, sensitivity labels, access controls, audit logs, workspace
lifecycle, and monitoring dataset refresh and usage metrics.
9. Performance Tuning & Optimization
Q: How to reduce PBIX file size?
A: Remove unused columns, reduce cardinality, change column data types to smaller types, turn off
'Enable Load' for staging queries, use aggregations, and remove unnecessary visuals.

Q: How to speed up refreshes?

A: Use query folding, incremental refresh, limit columns/rows imported, optimize source queries, and use
dedicated gateways/resources.

Q: How to troubleshoot slow visuals?

A: Use Performance Analyzer, check DAX query plans, reduce visual complexity, limit
cross-high-cardinality slicers, and cache results where possible.

Q: When to use aggregations?

A: Use aggregations for very large fact tables to serve common queries from aggregated table and fall
back to detailed data when needed.
10. Scenario-Based Interview Questions (with suggested approaches)
Scenario: Your report is extremely slow with a 100M rows fact table. What will you do?
Approach: Consider aggregations, import mode for relevant slices, incremental refresh, reduce columns,
create summary tables, use composite models or materialized views in source, and consider Premium
capacity for large memory.

Scenario: You need to implement RLS for a retail chain where each user should only see stores in
their region. How to design?
Approach: Create a mapping table (Store -> Region -> EmployeeEmail), implement dynamic RLS DAX on
the Store or Region table using USERPRINCIPALNAME(), test roles in Desktop, and assign security in
Service. Avoid hardcoding; use lookup table.

Scenario: You have to join two large tables but joins are slow in Power Query. What alternatives
exist?
Approach: Push join to source (query folding), create views in source DB, use staging tables, or use
relationships and perform minimal transformation in Power Query.

Scenario: Business asks for 'same store sales' comparison month-over-month. How to implement?
Approach: Create a proper Date table, mark as Date, build measures using DATEADD,
PARALLELPERIOD or SAMEPERIODLASTYEAR depending on need, and ensure correct filters for 'same
store' by using store context.

Scenario: You need to allow users to export underlying data but must hide PII columns. How to
handle?
Approach: Use sensitive data classification, dataset permissions, create separate view without PII for
exported usage, or create role-based views and implement RLS to restrict columns; use data protection in
Service.
11. Practical / Hands-on Tasks and Sample Tables
Below are sample hands-on tasks and a sample table structure you can present in interviews for
demonstrations.
Task 1: Build a Sales dashboard with KPIs: Total Sales, YoY Growth, Top 5 Products, Sales by
Region, and Monthly Trend. Provide steps and key measures.

Task 2: Implement dynamic RLS for managers to see only their teams' data. Provide DAX and
mapping table.

Task 3: Optimize a slow PBIX: list step-by-step actions and execute using Performance Analyzer.

Sample tables (provide in interview demonstrations):

| SalesOrderID | OrderDate | StoreID | ProductID | Quantity | UnitPrice | SalesAmount | CustomerID |
|--------------|-------------|---------|-----------|----------|-----------:|------------:|-----------:|
| 1001 | 2024-01-05 | S001 | P1001 | 2 | 499.00 | 998.00 | C001 |
| 1002 | 2024-01-07 | S002 | P1002 | 1 | 1299.00 | 1299.00 | C002 |

Appendix: Quick Cheat-sheet (useful DAX snippets)

YTD Sales: TOTALYTD([Total Sales], 'Date'[Date])

Running Total: CALCULATE([Total Sales], FILTER(ALLSELECTED('Date'[Date]), 'Date'[Date] <=

MAX('Date'[Date])))

Dynamic RLS filter example: [UserEmail] = USERPRINCIPALNAME()

Prepared by: Your Power BI Mentor (generated for interview preparation).