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.

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"

🔹 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