Power BI interview Questions

on

✅ POWER BI INTERVIEW ANSWER SHEET



🔹 Round 1 — Fundamentals

1️⃣ What are the main components of Power BI?

  • Power BI Desktop

  • Power BI Service (Cloud SaaS platform)

  • Power BI Mobile

  • Power BI Gateway

  • Power BI Report Builder (Paginated Reports)

  • Power BI Embedded (For developers)

2️⃣ Difference between Power BI Desktop and Power BI Service

Feature Power BI Desktop Power BI Service
Purpose         Report creation & modelling                 Sharing, collaboration, scheduled refresh
Storage Mode         Local PBIX file                 Cloud storage
Data Prep         Full Power Query & modelling                 Limited transformations
Publishing         Creates PBIX                 Consumes PBIX

3️⃣ Calculated Column vs Measure

Feature Calculated Column Measure
Calculated         Row-level         Aggregation-level
Stored in model         Yes (consumes memory)         No (calculated at query time)
Best for         Filtering, grouping, relationships         KPIs, dynamic calculations

Rule:
👉 Use a calculated column when the value is fixed per row.
👉 Use a measure when value must change based on filters.


a calculated column performs a row-by-row calculation and stores the static results in memory, while a measure performs a dynamic calculation based on the user's applied filters and is calculated on the fly at query time




4️⃣ What is a Star Schema and why use it?

A Star Schema consists of:

  • Fact Table → Transactions, numeric values

  • Dimension Tables → Descriptive attributes (date, product, supplier)

Why preferred?

  • Better compression

  • Faster query engine (VertiPaq optimized)

  • Clean relationships (1-to-many)

  • Simpler DAX

5️⃣ What is Row-Level Security (RLS)?

RLS restricts data visibility for different users.

Steps:

  1. Create security role (Manage Roles → Define filter)

  2. Assign user security group in Power BI Service

  3. Validate using "View as Roles"


6) What are the different types of views available in Power BI?

In Power BI Desktop
Report View: 
    This is the primary view for creating visualizations, arranging them on a canvas, and building multi-        page reports.
Data View: 
       Allows you to see your data in a table format. You can sort, search, and perform exploratory data             analysis or cleaning here.
Model View: 
        Shows a graphical representation of the relationships between your tables. You can manage and             modify these relationships in this view.
 
In the Power BI Service
Editing View: 
        Used by report designers to create, edit, and explore reports. It includes all functionality of                     Reading View plus more design and editing tools.
Reading View: 
        This is the mode for users who consume reports. It allows for interaction with the report but does             not include editing capabilities. 

7) What is the difference between DirectQuery and Live Connection in Power BI?

  • The main difference is that DirectQuery connects to an external data source like a database, while Live Connection connects to an existing Power BI semantic model (published in the Power BI service) or Analysis Services model. 
  • DirectQuery queries the source system directly, allowing for real-time data and the creation of calculated columns/measures within Power BI, whereas Live Connection relies on the pre-built model from the Analysis Services or Power BI service, providing the analytical engine of the source model but limiting some modeling capabilities in Power BI

8) Filter vs Slicer


Feature Filter Slicer
Location     Filters pane     Report canvas (visual object)
User Interaction     Low (mainly designer)     High (end users control it)
Visibility to user     Mostly hidden     Visible
Use Case     Internal filtering & preparation     Interactive data exploration
Appearance     Not customizable visually     Customizable with styles & formatting
Sync between pages     Yes, but limited     Can sync slicers across pages



Use Case        Recommended
Restrict data permanently (e.g., show only 2024 data)         Filter
Give user flexibility to change view dynamically         Slicer
Hide confidential values         Filter
Change product category, region, time for analysis         Slicer
Reduce page clutter         Filter
Provide interactive experience         Slicer

Type Example
List / Dropdown         Product, Region
Date range slider         Last 30 days, fiscal year
Hierarchy slicer         Country → State → City
Numeric range          Price range


🔹 Round 2 — DAX Knowledge

6️⃣ Explain CALCULATE()

CALCULATE modifies the current filter context and returns an expression with new filters applied.

Example:

Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

7️⃣ ALL() vs ALLEXCEPT() vs ALLSELECTED()

Function Behavior
ALL() Removes all filters from a column/table
ALLEXCEPT() Keeps specified columns filtered, removes others
ALLSELECTED() Removes filters but respects visuals/slicer interactions

8️⃣ YoY Growth DAX

YoY Growth % =
VAR PrevYear = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
DIVIDE([Total Sales] - PrevYear, PrevYear)

9️⃣ RELATED() vs RELATEDTABLE()

  • RELATED() → Brings a value from one-side of a relationship into the many side.

  • RELATEDTABLE() → Returns a table of matching records from related table.

Example use case: lookup description column in a fact table.

🔟 When to use VAR?

Use VAR to:

  • Improve readability

  • Avoid repeated calculations

  • Improve performance

🔹 Round 3 — Modelling & ETL

1️⃣1️⃣ How to validate auto-generated relationships?

  • Check primary/foreign key logic

  • Validate cardinality (1→many preferred)

  • Ensure correct filter direction

  • Avoid many-to-many unless necessary

1️⃣2️⃣ Handling many-to-many relationships

Options:

  • Create bridging table

  • Use DAX function TREATAS()

  • Avoid bidirectional filters unless controlled

1️⃣3️⃣ DirectQuery vs Import

Mode When to Use
Import                                 Smaller dataset, faster performance
DirectQuery                                 Real-time reporting, large datasets, compliance restrictions

1️⃣4️⃣ Handling Slowly Changing Dimensions (SCD)

Best handled at ETL layer using:

  • History tracking columns

  • Effective date / Expiry date

  • Surrogate keys

Power BI can use snapshots or incremental refresh for history.

1️⃣5️⃣ Circular dependency fix

  • Remove unnecessary calculated columns

  • Normalize logic using measures instead

  • Fix relationship design

🔹 Round 4 — Performance Optimization

1️⃣6️⃣ Improving slow report performance

  • Reduce cardinality

  • Proper star schema

  • Limit visuals per page

  • Use measures instead of calculated columns

  • Disable auto date/time

  • Turn off bidirectional filters unless needed

1️⃣7️⃣ Optimize DAX

  • Use VAR

  • Avoid iterators unless necessary

  • Use SUMX only when column context is needed

  • Reduce nested FILTER() functions

1️⃣8️⃣ When to use Aggregations

  • When dataset is extremely large (millions of rows)

  • When historical summarized trends needed vs granular drill-downs

1️⃣9️⃣ Bidirectional filtering impact

Pros: Fixes ambiguity in relationships
Cons: Can cause filter propagation issues and performance drop
Use only when required.

2️⃣0️⃣ Incremental refresh

Used for large datasets updated frequently.
Stores only changes rather than full refresh.

🔹 Round 5 — Real-World Scenarios

2️⃣1️⃣ Report vs SQL mismatch troubleshooting

  • Check filter context

  • Time intelligence logic

  • ETL refresh delays

  • Row-level security

  • Aggregation differences

2️⃣2️⃣ Usability design approach

  • Use bookmarks, tooltips, drillthrough

  • Slicers placed logically

  • KPI cards and trends prioritized

  • Avoid clutter

2️⃣3️⃣ Refresh every 5 minutes considerations

  • Capacity SKU

  • Gateway load

  • Data source capability

  • Licensing (Premium required)

2️⃣4️⃣ Fix disconnected tables

  • Consolidate using lookup or bridging tables

  • Create clear dimension hierarchy

  • Remove unused tables/columns

2️⃣5️⃣ Dynamic KPI measure

Use a disconnected table + SELECTEDVALUE() + SWITCH() logic.

🔹 Bonus Deep Dive

2️⃣6️⃣ FILTER vs CALCULATE context transition

  • FILTER() creates a row context inside DAX

  • CALCULATE() converts row context into filter context

2️⃣7️⃣ Evaluation Context Types

  • Row context

  • Filter context

  • Query context

2️⃣8️⃣ Dynamic security

Use USERPRINCIPALNAME() in RLS filters.

2️⃣9️⃣ Disable auto date/time?

Saves memory and improves performance in large datasets.

3️⃣0️⃣ Composite Models

Allows DirectQuery + Import together. Useful for large real-time models.

⭐ You’re now fully prepared.

Would you like:

➡ Mock rapid-fire test (20 questions)?
➡ Hands-on scenario assignments with answers?

Just say: “Mock test” or “Give practice tasks.”

Comments

Post a Comment