Deloitte Recent Interview Insights for a
Power BI Developer Position (0-3 Years)
Power BI Basics
1. What are the key features of Power BI?
Power BI is a powerful business intelligence tool with the following key features:
1. Data Connectivity:
o Connects to a wide variety of data sources like Excel, SQL Server, SharePoint,
Azure, etc.
2. Interactive Dashboards:
o Offers real-time dashboards and rich visualizations to monitor data
effectively.
3. Data Modeling:
o Allows creating relationships between tables, defining calculated measures,
and implementing DAX (Data Analysis Expressions) for advanced analytics.
4. Custom Visualizations:
o Supports importing custom visuals from the AppSource Marketplace or
developing new ones.
5. AI-Powered Insights:
o Features like Q&A (natural language queries) and AI visuals like Key
Influencers to uncover hidden patterns in data.
6. Row-Level Security (RLS):
o Ensures users view only data they are authorized to see.
7. Power BI Service:
o Facilitates sharing, collaboration, and publishing reports/dashboards online.
8. Mobile App:
o Enables access to reports and dashboards on the go via the Power BI Mobile
app.
2. Explain the difference between Power BI Desktop, Power BI
Service, and Power BI Mobile.
Feature Power BI Desktop Power BI Service Power BI Mobile
Data preparation, View and interact
Online platform for sharing
Purpose modeling, and report with reports on
and collaboration.
creation. mobile devices.
Used by end users to view
Primarily used by Designed for mobility
Usage and share
developers. and accessibility.
reports/dashboards.
Mobile app installed
Installed on local Cloud-based; no
Installation on smartphones or
machines. installation needed.
tablets.
Connects to on-
Data Refreshes datasets and Access pre-published
premises and cloud
Connectivity schedules data updates. reports.
data sources.
Limited
Limited
Facilitates sharing, collaboration;
Collaboration collaboration; works
commenting, and alerts. primarily for viewing
offline.
reports.
3. What are the different data connectivity modes in Power BI
(Import, DirectQuery, Live Connection)?
1. Import Mode:
• Description:
o Loads data into Power BI’s in-memory model. Reports query the imported
data, making them faster.
• Advantages:
o Best for high performance.
o Allows extensive DAX calculations.
• Limitations:
o Dataset size is limited to memory capacity.
o Requires refreshing data manually or via scheduled refresh.
2. DirectQuery Mode:
• Description:
o Does not import data but queries the source database in real time for every
interaction.
• Advantages:
o No size restrictions on the dataset.
o Real-time updates from the data source.
• Limitations:
o Slower performance for complex queries.
o Limited DAX functionality compared to Import mode.
3. Live Connection:
• Description:
o Connects directly to a Power BI dataset or SSAS (SQL Server Analysis
Services).
• Advantages:
o Ideal for multi-developer environments.
o No data storage in Power BI; all queries are executed on the underlying data
source.
• Limitations:
o Only one connection is allowed per report.
o DAX creation and data transformations are limited.
4. How do you handle row-level security (RLS) in Power BI?
Row-Level Security (RLS) restricts data access based on user roles. Here’s how you
implement it:
Steps to Implement RLS:
1. Define Roles in Power BI Desktop:
o Go to the Modeling tab and select Manage Roles.
o Create a role (e.g., "Region Manager").
o Apply a DAX filter to the relevant table. Example:
DAX
[Region] = USERNAME()
2. Test Roles in Power BI Desktop:
o Use the “View as Roles” feature to verify data restrictions for specific roles.
3. Publish to Power BI Service:
o Publish the report to the Power BI Service.
4. Assign Users to Roles:
o In the Power BI Service, navigate to the dataset.
o Go to the Security tab and assign users to the defined roles.
Best Practices:
• Ensure sensitive columns are excluded from view.
• Use Azure Active Directory for seamless integration with organizational security
policies.
• Regularly test and validate RLS configurations for accuracy.
Data Modeling
5. How do you create relationships between tables in Power BI?
Power BI allows you to create relationships between tables to enable efficient querying and
data modeling.
Steps to Create Relationships:
1. Access the Model View:
o Navigate to the “Model” view in Power BI Desktop.
o Drag and drop the tables into the canvas to visualize the relationships.
2. Automatic Relationship Detection:
o Power BI automatically detects relationships if column names are similar
(e.g., "CustomerID" in two tables).
3. Manual Relationship Creation:
o Go to Modeling > Manage Relationships > New.
o Define the relationship by selecting:
▪ Tables: Choose the source and target tables.
▪ Columns: Specify the key columns used for the relationship.
▪ Cardinality: Choose between One-to-One, One-to-Many, or Many-to-
Many.
▪ Cross-filter Direction: Choose Single or Both for data propagation
across tables.
4. Active vs. Inactive Relationships:
o Only one active relationship is allowed between tables at a time. Inactive
relationships are denoted by a dotted line and can be activated using DAX
functions like USERELATIONSHIP.
5. Best Practices:
o Ensure that primary and foreign key relationships are clearly defined.
o Avoid duplicate keys in tables to prevent relationship errors.
6. What is the difference between a star schema and a
snowflake schema? When do you use each?
Star Schema:
• Structure:
o Central fact table connected directly to dimension tables (flat structure).
o Dimension tables are denormalized, storing all descriptive attributes in one
table.
• Advantages:
o Simple and intuitive design.
o Faster querying due to fewer joins.
o Best for reporting and dashboarding.
• When to Use:
o Ideal for performance-oriented BI tools like Power BI.
o Use when simplicity and query speed are priorities.
Snowflake Schema:
• Structure:
o Central fact table connected to normalized dimension tables (hierarchical
structure).
o Dimension tables are split into sub-dimensions based on attributes.
• Advantages:
o Reduces data redundancy.
o Easier to maintain and update (better for large-scale databases).
• When to Use:
o Useful for OLAP (Online Analytical Processing) systems.
o Use when storage optimization and data normalization are critical.
Comparison:
Feature Star Schema Snowflake Schema
Complexity Simple More complex
Performance Faster querying Slower due to multiple joins
Redundancy Higher Lower
Use Case Reporting and analytics Data warehouses with strict normalization
7. Explain the importance of surrogate keys in data modeling.
Definition:
• Surrogate keys are system-generated unique identifiers used as primary keys in a
table (e.g., integers, GUIDs).
Importance:
1. Uniqueness:
o Guarantees a unique key for every record, ensuring reliable relationships.
2. Decoupling:
o Surrogate keys are independent of business logic, allowing changes in
business keys (e.g., customer ID) without affecting relationships.
3. Performance:
o Numeric surrogate keys improve join performance compared to text-based
keys.
4. Consistency Across Systems:
o Ensures data integrity when integrating data from multiple sources with
different primary keys.
5. Handles Nulls:
o Business keys can be null, but surrogate keys are always unique and non-
null.
Example:
CustomerKey CustomerID CustomerName
1 C123 John Smith
2 C124 Jane Doe
8. How do you handle circular relationships in Power BI?
Definition:
• Circular relationships occur when tables are linked in a way that creates a loop (e.g.,
A → B → C → A).
Why It’s a Problem:
• Causes ambiguity in data propagation.
• Can result in errors or incorrect calculations.
Ways to Handle Circular Relationships:
1. Redesign the Data Model:
o Remove unnecessary relationships and optimize table structure.
o Ensure there are no redundant paths between tables.
2. Use Bridge Tables:
o Introduce a bridge table to break the loop.
o Example: If Table A, Table B, and Table C form a loop, create a new table
(Bridge) to handle shared keys.
3. Set Relationships as Inactive:
o Use inactive relationships to break the loop and activate them in calculations
using the USERELATIONSHIP function.
4. Cross-Filter Direction:
o Use Single Direction filtering instead of Both to control data flow.
5. DAX Measures:
o For calculations involving circular paths, use DAX to control filtering
explicitly.
9. What are calculated columns and measures? How are they
different?
Calculated Columns:
• Definition:
o A column created using DAX formulas and added to the data model.
o Calculated once during data load or refresh and stored in memory.
• Use Case:
o To create new fields based on existing columns.
o Example:
DAX
Total Sales = [Quantity] * [Price]
• Performance:
o Consumes more memory as the values are stored with the dataset.
Measures:
• Definition:
o Dynamic calculations performed on the fly during report interactions.
o Calculated using DAX but not stored in the dataset.
• Use Case:
o Aggregate calculations like sums, averages, or percentages.
o Example:
DAX
Total Sales = SUM([Sales])
• Performance:
o More efficient as they are calculated only when required.
Comparison:
Feature Calculated Column Measure
Storage Stored in the model Calculated at runtime
Performance Impact Higher (consumes memory) Lower (dynamic calculations)
Use Case Row-level calculations Aggregate-level calculations
DAX (Data Analysis Expressions)
10. Write a DAX formula to calculate year-to-date (YTD) sales.
YTD Sales Calculation:
The TOTALYTD function is commonly used to calculate year-to-date (YTD) values.
Formula:
DAX
YTD Sales = TOTALYTD(SUM(Sales[SalesAmount]), Dates[Date])
Explanation:
1. TOTALYTD:
o Aggregates values from the start of the year to the specified date.
2. SUM(Sales[SalesAmount]):
o Calculates the total sales amount for each row.
3. Dates[Date]:
o Specifies the date column from a proper Date table to determine the year-to-
date range.
4. Ensure you have a properly formatted Date Table marked as "Date Table" in Power
BI.
11. What is the difference between the SUM, SUMX, and
CALCULATE functions?
1. SUM:
• Definition: Aggregates (adds) all values in a single column.
• Syntax:
DAX
SUM(ColumnName)
• Example:
DAX
Total Sales = SUM(Sales[SalesAmount])
• Use Case: Simple aggregations on a column.
2. SUMX:
• Definition: Performs row-by-row calculations and then sums up the result.
• Syntax:
DAX
SUMX(Table, Expression)
• Example:
DAX
Total Sales = SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])
• Use Case: Use when the calculation depends on multiple columns or row-by-row
evaluation.
3. CALCULATE:
• Definition: Modifies the context of a calculation by applying filters.
• Syntax:
DAX
CALCULATE(Expression, Filters...)
• Example:
DAX
Sales for USA = CALCULATE(SUM(Sales[SalesAmount]), Sales[Country] = "USA")
• Use Case: Use to apply custom filters or modify the existing context.
Comparison:
Function Purpose Use Case
SUM Simple column aggregation Adding up values in one column
Calculating totals based on expressions (e.g.,
SUMX Row-by-row aggregation
Quantity * Price)
Context modification for
CALCULATE Adding filters or modifying filter context
aggregations
12. How do you use the FILTER function in DAX? Provide an
example.
FILTER Function:
• Definition: Returns a table that meets a specified condition.
• Syntax:
DAX
FILTER(Table, Expression)
Example:
1. Scenario: Calculate total sales for orders with an amount greater than $1000.
DAX
High Value Sales =
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(Sales, Sales[SalesAmount] > 1000)
Explanation:
• FILTER(Sales, Sales[SalesAmount] > 1000):
o Filters the Sales table to include only rows where SalesAmount > 1000.
• CALCULATE:
o Applies the filter and sums up the SalesAmount.
Key Notes:
• FILTER always returns a table, not a scalar value.
• It’s often used with functions like CALCULATE to modify the filter context.
13. Explain the concept of context in DAX (row context and filter
context).
1. Row Context:
• Definition: The context in which calculations are performed row by row.
• Occurs In: Calculated columns, SUMX, AVERAGEX, etc.
• Example:
DAX
Line Total = Sales[Quantity] * Sales[UnitPrice]
o Each row has its own context for the calculation.
2. Filter Context:
• Definition: The set of filters applied to a calculation, either from visuals (slicers,
filters) or explicitly defined in DAX.
• Occurs In: Measures and functions like CALCULATE, FILTER, etc.
• Example:
DAX
Total Sales for USA = CALCULATE(SUM(Sales[SalesAmount]), Sales[Country] = "USA")
o The filter context limits the calculation to rows where the Country is "USA".
Comparison:
Context
Definition Use Case
Type
Used in calculated columns or iterators like
Row Context Row-by-row evaluation
SUMX
Filter Filters applied to modify Used in measures, slicers, and CALCULATE
Context results expressions
14. Write a DAX formula to calculate the running total for sales.
Running Total Calculation:
The CALCULATE and FILTER functions are commonly used to compute running totals.
Formula:
DAX
Running Total =
CALCULATE(
SUM(Sales[SalesAmount]),
FILTER(
ALL(Dates),
Dates[Date] <= MAX(Dates[Date])
Explanation:
1. SUM(Sales[SalesAmount]):
o Adds up the sales amount.
2. ALL(Dates):
o Removes any existing filters on the Dates table.
3. Dates[Date] <= MAX(Dates[Date]):
o Ensures the calculation includes all dates up to the current date.
4. CALCULATE:
o Applies the filter and evaluates the sum.
Key Notes:
• Use a properly marked Date table for accurate calculations.
• Running totals depend on sorting by the date column in the visual.
Power Query and Data Transformation
15. How do you handle missing or inconsistent data in Power
Query?
Handling Missing Data:
Power Query provides several tools to deal with missing data:
1. Replace Values:
o Replace missing or null values with default values (e.g., 0, "Unknown").
o Steps:
▪ Select the column.
▪ Go to Transform → Replace Values → Enter values to replace and
replace with.
▪ Example:
[Link](#"Previous Step", null, "Unknown", [Link],
{"ColumnName"})
2. Remove Rows with Missing Data:
o Remove rows where critical values are missing.
o Steps:
▪ Go to Home → Remove Rows → Remove Blank Rows.
▪ Example:
[Link](#"Previous Step", each [ColumnName] <> null)
3. Fill Missing Data:
o Fill missing values using values from above or below.
o Steps:
▪ Select column → Transform → Fill Down or Fill Up.
▪ Example:
[Link](#"Previous Step", {"ColumnName"})
4. Filter Missing Data:
o Filter rows with missing values.
o Steps:
▪ Go to Filter Arrow → Uncheck "null" values.
Handling Inconsistent Data:
1. Trim and Clean:
o Remove extra spaces or non-printable characters.
o Steps:
▪ Go to Transform → Format → Trim or Clean.
▪ Example:
[Link](#"Previous Step", {{"ColumnName", [Link]}})
2. Convert Data Types:
o Ensure consistent data types.
o Steps:
▪ Select column → Transform → Data Type.
3. Remove Duplicates:
o Remove duplicate rows.
o Steps:
▪ Select columns → Home → Remove Duplicates.
4. Error Handling:
o Use Replace Errors to replace invalid values.
o Steps:
▪ Go to Transform → Replace Errors.
16. Explain the concept of M language in Power Query.
What is M Language?
• Definition: M (Power Query Formula Language) is a functional, case-sensitive
programming language used in Power Query for data transformation and shaping.
• Purpose: It automates and customizes data transformations behind the scenes.
Key Features of M Language:
1. Functional Language:
o Operations are performed using functions, e.g., [Link]().
2. Case-Sensitive:
o Names and expressions are case-sensitive.
3. Step-Based:
o Each transformation step in Power Query generates an M script.
Example M Code:
• Sample transformation code:
let
Source = [Link]([Link]("C:\Data\[Link]"), null, true),
Filtered = [Link](Source, each [Sales] > 1000),
RenamedColumns = [Link](Filtered, {{"Sales", "HighSales"}})
in
RenamedColumns
Commonly Used M Functions:
1. [Link]() – Filters rows based on conditions.
2. [Link]() – Adds a custom column.
3. [Link]() – Renames columns in a table.
Custom M Script Editor:
• In Power Query, use the Advanced Editor to view and edit M code.
17. How do you split columns or merge tables in Power Query?
Splitting Columns:
• Splitting columns is used to divide data based on delimiters, number of characters,
or position.
1. Split by Delimiter:
o Example: Split "FirstName LastName" into two columns.
o Steps:
▪ Select column → Split Column → By Delimiter.
▪ Example M Code:
[Link](#"Previous Step", "Name", [Link](" "),
{"FirstName", "LastName"})
2. Split by Number of Characters:
o Example: Split "202501" into "Year" and "Month".
o Steps:
▪ Select column → Split Column → By Number of Characters.
▪ Example M Code:
[Link](#"Previous Step", "Date", [Link](4, 2),
{"Year", "Month"})
Merging Tables:
• Merging tables allows you to combine data based on relationships (similar to SQL
joins).
1. Merge Queries:
o Example: Merge Orders table with Customers table on CustomerID.
o Steps:
▪ Go to Home → Merge Queries.
▪ Select the common column (e.g., CustomerID) from both tables.
▪ Choose join type (Inner, Outer, Left, Right, etc.).
▪ Example M Code:
[Link](Orders, {"CustomerID"}, Customers, {"CustomerID"}, "MergedTable",
[Link])
2. Expand Merged Table:
o After merging, expand the resulting table to include desired columns.
18. What is the difference between appending and merging
queries in Power Query?
Appending Queries:
• Definition: Combines rows from two or more tables into a single table (like a UNION
in SQL).
• Use Case: Use when the structure of the tables is identical, and you want to stack
rows together.
• Steps:
o Go to Home → Append Queries → Choose tables to append.
• Example: Combine Sales_Q1 and Sales_Q2.
M
[Link]({Sales_Q1, Sales_Q2})
Merging Queries:
• Definition: Combines columns from two or more tables based on relationships (like
JOINs in SQL).
• Use Case: Use when you need to combine data from related tables using a
common key (e.g., CustomerID).
• Steps:
o Go to Home → Merge Queries → Choose tables and join conditions.
• Example: Merge Orders and Customers based on CustomerID.
[Link](Orders, {"CustomerID"}, Customers, {"CustomerID"}, "MergedTable",
[Link])
Comparison:
Aspect Appending Queries Merging Queries
Purpose Combine rows (union) Combine columns (join)
Tables must have identical Tables can have different
Data Structure
structure structures
Key
No relationship needed Common key required
Requirement
Data Visualization
19. How do you choose the right type of visualization for your
data?
Choosing the right type of visualization depends on the type of data and the message you
want to convey. Here are key considerations:
1. Understand Your Data
• Identify the data type: categorical, numerical, or time-series.
• Analyze relationships: Is it a comparison, trend, distribution, or composition?
2. Choose Visualization Based on Purpose
Purpose Recommended Visualization
Comparisons Bar Chart, Column Chart, Line Chart
Trends Over Time Line Chart, Area Chart
Proportions/Composition Pie Chart, Donut Chart, Stacked Bar/Column Chart
Distributions Histogram, Box Plot, Scatter Plot
Relationships Scatter Plot, Bubble Chart
Geographical Data Map Visuals (Filled Map, Shape Map)
3. Best Practices
• Use clear labels, legends, and axis titles.
• Avoid clutter and excessive use of colors.
• Use consistent scales for comparison.
Example:
To compare sales across regions, use a bar chart. For sales trends over months, use a line
chart.
20. What are slicers, and how do you use them in Power BI
dashboards?
Definition:
• Slicers are interactive filters in Power BI that allow users to slice and dice data
based on specific criteria (e.g., region, product, date).
Types of Slicers:
1. Dropdown Slicers: Use when space is limited.
2. List Slicers: Display multiple options visibly.
3. Hierarchical Slicers: Enable filtering through multiple levels (e.g., Year → Month →
Day).
4. Date Slicers: Allow filtering by specific dates or ranges.
How to Use Slicers:
1. Add a Slicer:
o Drag a field (e.g., Region) into the slicer visual.
o Select Slicer from the visualization pane.
2. Customize Slicers:
o Change the slicer type: Dropdown, List, or Relative Date.
o Enable multi-selection to select multiple values.
3. Sync Slicers Across Pages:
o Go to View → Sync Slicers → Apply slicer settings to multiple pages.
Use Case:
A slicer for "Product Category" allows users to view sales for specific categories like
"Electronics" or "Clothing."
21. Explain the use of bookmarks in Power BI.
Definition:
• Bookmarks in Power BI save the state of a report page, including filters, slicer
selections, and visual states, to provide predefined views of the data.
Use Cases:
1. Switch Between Views:
o Save different states (e.g., filtered views for "East Region" and "West
Region").
2. Custom Navigation:
o Use bookmarks with buttons to create interactive report navigation.
3. Highlight Insights:
o Directly jump to a specific insight or scenario for storytelling.
How to Create a Bookmark:
1. Set up the report page (filters, slicers, visuals).
2. Go to View → Bookmarks Pane.
3. Click Add Bookmark and rename it.
Using Bookmarks:
• Add Buttons or Shapes → Link them to bookmarks for interactive navigation.
22. How do you create a drill-through page in Power BI?
Definition:
• Drill-through pages allow users to focus on specific details about a data point by
navigating to a detailed report page.
Steps to Create a Drill-Through Page:
1. Create a Drill-Through Page:
o Add a new page → Drag the field you want to drill through (e.g., Region) into
the Drill-through Filters section.
o Customize the page visuals to show detailed insights for the selected value.
2. Add a Back Button:
o Insert a button from Insert → Back.
o This allows users to return to the original report.
3. Enable Drill-Through:
o Right-click on a data point in the original report.
o Select Drill-through → Navigate to the drill-through page.
Example:
A summary page with sales by region can drill through to a detailed sales page showing
product-wise data for a selected region.
23. What are custom visuals, and how do you import them into
Power BI?
Definition:
• Custom visuals are third-party visualizations created using Power BI's development
framework to provide enhanced and tailored visuals.
Why Use Custom Visuals?:
1. Add unique visuals not available in the default library (e.g., Gantt charts, bullet
charts).
2. Enhance user experience with specialized visuals.
Steps to Import Custom Visuals:
1. Download Custom Visuals:
o Visit the Microsoft AppSource marketplace
([Link]
o Search for and download the desired custom visual.
2. Import into Power BI:
o Go to the Visualizations Pane.
o Click More Options (three dots) → Import a Visual from File.
o Select the .pbiviz file and import it.
3. Use the Custom Visual:
o Drag the custom visual from the visualization pane to the report canvas.
Example:
To create a project timeline, import a Gantt Chart custom visual and map fields like task,
start date, and end date.