Order Year = YEAR(Sales[OrderDate])

But I avoid creating unnecessary calculated columns because they increase model size.

4. Explain Filter Context and Row Context.

This is a very common senior DAX interview question.

Filter Context

Filter context is the set of filters applied to a calculation.

For example:

Total Sales =
SUM(Sales[SalesAmount])

If the report has:

Country = India
Year = 2026

then the measure calculates sales only for India in 2026.

Row Context

Row context means DAX is evaluating one row at a time.

Example:

Profit = Sales[SalesAmount] - Sales[Cost]

A calculated column evaluates this expression for every row.

5. What is CALCULATE and why is it so important?

CALCULATE() is one of the most important DAX functions.

It modifies the filter context in which an expression is evaluated.

Example:

Total Sales =
SUM(Sales[SalesAmount])

Now suppose we want sales only for India:

India Sales =
CALCULATE(
    [Total Sales],
    DimCountry[Country] = "India"
)

Another example

Previous Year Sales =
CALCULATE(
    [Total Sales],
    DATEADD(
        DimDate[Date],
        -1,
        YEAR
    )
)

Senior-level explanation

I use CALCULATE extensively for:

• Time intelligence
• Conditional calculations
• Dynamic filtering
• Role-based calculations
• Percentage calculations
• Prior-year comparisons
• Removing filters

6. Explain ALL vs ALLSELECTED vs REMOVEFILTERS.

ALL

Removes filters.

Total Sales All Countries =
CALCULATE(
    [Total Sales],
    ALL(DimCountry)
)

This ignores the country filter.

REMOVEFILTERS

More explicitly communicates that we want to remove filters.

Total Sales All Countries =
CALCULATE(
    [Total Sales],
    REMOVEFILTERS(DimCountry)
)

I generally prefer REMOVEFILTERS when my intention is specifically to remove filters.

ALLSELECTED

This is useful when you want to respect the user’s external selections but ignore some visual-level context.

For example:

% of Selected Sales =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        ALLSELECTED(DimProduct)
    )
)

This is useful for:

• % of selected total
• Ranking within selected filters
• Visual comparisons

7. Explain HASONEVALUE vs SELECTEDVALUE.

HASONEVALUE

Checks whether exactly one value is selected.

HASONEVALUE(DimProduct[Category])

Returns TRUE/FALSE.

SELECTEDVALUE

Returns the selected value if exactly one value is selected.

Selected Category =
SELECTEDVALUE(
    DimProduct[Category],
    "Multiple Categories"
)

If:

Category = Electronics

Result:

Electronics

If multiple categories are selected:

Multiple Categories

8. How would you calculate YoY growth?

Sales LY =
CALCULATE(
    [Total Sales],
    DATEADD(
        DimDate[Date],
        -1,
        YEAR
    )
)

Then:

YoY Growth % =
DIVIDE(
    [Total Sales] - [Sales LY],
    [Sales LY]
)

Or:

YoY Growth % =
DIVIDE(
    [Total Sales],
    [Sales LY]
) - 1

Example

Current year:

₹120 Cr

Previous year:

₹100 Cr

YoY = (120 - 100) / 100
    = 20%

So sales increased by 20%.

9. Why do we need a Date table?

A proper Date dimension is important for:

• YoY
• MoM
• YTD
• MTD
• QTD
• Rolling periods
• Fiscal calendars
• Time intelligence

Example:

DimDate =
CALENDAR(
    DATE(2020,1,1),
    DATE(2030,12,31)
)

Then add:

Year
Month
Month Number
Quarter
Fiscal Year
Fiscal Month
Week

I also mark it as a Date Table in Power BI.

10. What is YTD?

YTD means Year To Date.

Example:

If today is August 22:

YTD sales means:

January 1 → August 22

DAX:

Sales YTD =
TOTALYTD(
    [Total Sales],
    DimDate[Date]
)

11. What is a running total?

A running total accumulates values over time.

Example:

Jan = 10
Feb = 20
Mar = 30

Running total:

Jan = 10
Feb = 30
Mar = 60

DAX:

Running Sales =
CALCULATE(
    [Total Sales],
    FILTER(
        ALLSELECTED(DimDate[Date]),
        DimDate[Date]
            <= MAX(DimDate[Date])
    )
)

12. Explain Power BI relationships.

Common relationship types:

• One-to-many
• Many-to-one
• One-to-one
• Many-to-many

For a typical star schema:

DimProduct (1)
       |
       | 
       *
FactSales

So:

DimProduct[ProductKey]
        1
        ↓
FactSales[ProductKey]
        *

I normally prefer one-to-many relationships with single-direction filtering.

13. Why should you avoid bidirectional relationships?

Bidirectional relationships can create:

• Ambiguous filter paths
• Unexpected results
• Performance issues
• Difficult-to-debug models

For example:

Customer → Sales
Customer ← Returns

can create complicated filter propagation.

I use bidirectional filtering only when there is a specific business/modeling requirement.

14. How do you handle many-to-many relationships?

Instead of directly connecting two tables:

Customer ↔ Product

I prefer using a bridge table.

Customer
   ↓
BridgeCustomerProduct
   ↓
Product

This gives more controlled filter propagation.

15. Import vs DirectQuery?

Import

Data is loaded into Power BI’s in-memory engine.

Advantages:

• Faster report performance
• Rich DAX capabilities
• Better visualization performance

Disadvantage:

• Data refresh is required.

DirectQuery

Data remains in the source.

Power BI sends queries to the source database.

Advantages:

• Near real-time data
• Large datasets can remain in source

Disadvantages:

• Performance depends heavily on source
• More limitations
• Complex DAX can generate expensive queries

Senior answer

I prefer Import mode whenever possible.

I use DirectQuery when requirements such as:

• Near real-time reporting
• Very large data
• Source governance requirements

justify it.

16. What is Composite Model?

A Composite Model allows you to combine different storage modes.

For example:

Import
+
DirectQuery

Example:

Historical Sales → Import

Today's transactions → DirectQuery

This allows a balance between:

performance + freshness.

17. What is Incremental Refresh?

Incremental refresh means instead of refreshing the entire dataset, Power BI refreshes only the required recent partitions.

Example:

2019
2020
2021
2022
2023
2024
2025
2026

Instead of refreshing everything, you might configure:

Store: 5 years
Refresh: last 7 days

Power BI retains historical partitions and refreshes the recent period.

Benefits

• Faster refresh
• Reduced source load
• Better scalability
• Useful for large fact tables

18. How do you optimize a slow Power BI report?

This is a very important senior interview question.

I follow a structured approach.

Step 1 — Identify the problem

I use:

• Performance Analyzer
• DAX Studio
• VertiPaq Analyzer
• SQL Server Profiler where applicable
• Source query performance tools

Step 2 — Check the data model

I look for:

• Huge tables
• High-cardinality columns
• Unnecessary columns
• Many-to-many relationships
• Bidirectional relationships
• Snowflake structures that could be simplified
• Unnecessary calculated columns

Step 3 — Optimize DAX

For example, avoid unnecessarily expensive iterator expressions.

Instead of:

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

where possible, pre-calculate the appropriate value in the data layer.

Step 4 — Optimize visuals

Don’t put 20–30 visuals on one page.

Reduce:

• Excessive visuals
• High-cardinality tables
• Complex slicers
• Unnecessary interactions

Step 5 — Optimize source queries

Push transformations to:

• SQL
• Data warehouse
• Lakehouse
• Power Query folding

where appropriate.

19. What is Query Folding?

Query folding means Power Query pushes transformations back to the source system.

For example:

Power BI
   ↓
Power Query
   ↓
SQL Server

If you filter:

Year = 2026

Power Query may generate SQL similar to:

SELECT *
FROM Sales
WHERE Year = 2026

instead of pulling all records into Power BI first.

Why important?

Query folding improves:

• Refresh performance
• Source utilization
• Data transfer
• Scalability

20. What transformations should be done in SQL vs Power Query?

SQL/Data Warehouse

I prefer putting:

• Heavy transformations
• Joins
• Aggregations
• Data cleansing
• Business-standardized dimensions

in the data platform where appropriate.

Power Query

I use it for:

• Lightweight transformations
• Source-specific cleansing
• Data type conversion
• Merging where appropriate
• Final semantic preparation

Senior answer

I avoid putting heavy business logic exclusively inside Power BI if the same logic needs to be reused across multiple reports.

21. Explain Row-Level Security.

RLS restricts the data a user can see.

Example:

Manager → India
Manager → UK
Manager → USA

Manager A should only see India.

Static RLS

Create a role:

DimCountry[Country] = "India"

Dynamic RLS

Create:

UserAccess

Email             Country
user1@abc.com     India
user2@abc.com     USA
user3@abc.com     UK

Role:

[Email] = USERPRINCIPALNAME()

Relationship:

UserAccess
    ↓
DimCountry
    ↓
FactSales

Now the logged-in user’s email determines the data they see.

22. What is USERPRINCIPALNAME()?

USERPRINCIPALNAME()

returns the current user’s identity, typically their email/UPN in Power BI Service scenarios.

Example:

[Email] = USERPRINCIPALNAME()

This is commonly used in dynamic RLS.

23. How do you test RLS?

In Power BI Desktop:

Modeling → View as

Then select the required role.

In Power BI Service, I verify:

• User membership
• Workspace/app access
• Semantic model permissions
• RLS role assignment

Important senior point

Giving a user Build permission or other permissions on a semantic model can affect how RLS is experienced, so security should be designed and tested at the workspace/semantic-model level as well.

24. What is a Power BI semantic model?

A semantic model is the business-friendly analytical layer containing:

• Tables
• Relationships
• Measures
• Calculated columns
• Hierarchies
• Metadata
• Security

For example:

FactSales
DimDate
DimProduct
DimCustomer
DimCountry

with measures:

Total Sales
Total Profit
Profit Margin %
YoY Growth %
YTD Sales

Reports connect to this semantic model.

Senior-level advantage

A reusable semantic model allows multiple reports to use the same governed business logic.

25. What is a thin report?

A thin report contains mostly:

• Visuals
• Filters
• Pages
• User experience

while the actual:

• Data model
• Measures
• Relationships
• Security

are maintained in a centralized semantic model.

Example:

               Semantic Model
              /       |       \
             /        |        \
       Sales Report HR Report Finance Report

This is useful for enterprise self-service BI.

26. How do you handle multiple fact tables?

Suppose you have:

FactSales
FactInventory
FactReturns
FactBudget

Don’t unnecessarily connect facts directly.

Instead:

          DimDate
         /   |   \
        /    |    \
FactSales FactInventory FactBudget
        \    |    /
         Dimensions

Use conformed dimensions.

For example:

DimDate
DimProduct
DimCustomer
DimCountry

can be shared across multiple fact tables.

27. What is a role-playing dimension?

Suppose Sales has:

OrderDate
ShipDate
DeliveryDate

All three represent dates.

You can have:

DimOrderDate
DimShipDate
DimDeliveryDate

or use one Date table with inactive relationships and activate the required relationship in measures.

Example:

Sales by Ship Date =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        Sales[ShipDate],
        DimDate[Date]
    )
)

28. Explain USERELATIONSHIP.

USERELATIONSHIP() temporarily activates an inactive relationship for a calculation.

Example:

DimDate[Date]
       |
       | active
       ↓
Sales[OrderDate]

DimDate[Date]
       |
       | inactive
       ↓
Sales[ShipDate]

Measure:

Ship Sales =
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(
        Sales[ShipDate],
        DimDate[Date]
    )
)

29. What is the difference between SUM and SUMX?

SUM

Aggregates a column.

Total Sales =
SUM(Sales[SalesAmount])

SUMX

Iterates row by row and evaluates an expression.

Revenue =
SUMX(
    Sales,
    Sales[Quantity] * Sales[UnitPrice]
)

Senior point

SUMX can be more expensive because it is an iterator, so I use it when row-level calculation is genuinely required.

30. What is the difference between COUNT, COUNTROWS and DISTINCTCOUNT?

COUNT

Counts numeric values.

COUNT(Sales[OrderID])

COUNTROWS

Counts rows.

COUNTROWS(Sales)

DISTINCTCOUNT

Counts unique values.

DISTINCTCOUNT(Sales[CustomerID])

For example:

Sales rows = 100,000
Unique customers = 8,000

Then:

COUNTROWS = 100,000

DISTINCTCOUNT(CustomerID) = 8,000

31. How would you implement Top 5 customers?

Top 5 Customer Sales =
CALCULATE(
    [Total Sales],
    TOPN(
        5,
        ALLSELECTED(DimCustomer[Customer]),
        [Total Sales],
        DESC
    )
)

Or use a ranking measure:

Customer Rank =
RANKX(
    ALLSELECTED(DimCustomer[Customer]),
    [Total Sales],
    ,
    DESC
)

Then filter:

Customer Rank <= 5

32. How would you calculate market share?

Market Share % =
DIVIDE(
    [Total Sales],
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS(DimProduct)
    )
)

The denominator removes the product filter while keeping other filters such as:

Country
Year
Division

33. What is a disconnected table?

A disconnected table has no relationship with the model.

It’s often used for:

• What-if analysis
• Parameter selection
• Dynamic measures
• User selections

Example:

Metric Selection

Sales
Profit
Quantity

Measure:

Selected Metric =
SWITCH(
    SELECTEDVALUE(Metric[Metric]),
    "Sales", [Total Sales],
    "Profit", [Total Profit],
    "Quantity", [Total Quantity]
)

34. How would you create a dynamic KPI?

Suppose the user wants:

Sales
Profit
Margin

Use a disconnected table and SWITCH.

Selected KPI =
SWITCH(
    SELECTEDVALUE(KPI[KPI]),
    "Sales", [Total Sales],
    "Profit", [Total Profit],
    "Margin", [Profit Margin %]
)

Then the same visual dynamically changes based on the user’s selection.

35. What is Power BI deployment pipeline?

Deployment Pipelines allow you to move content through environments:

Development
     ↓
Test
     ↓
Production

For example:

Development

Developers create:

• Data models
• Reports
• Measures

Test

Business users validate.

Production

Published for end users.

This reduces the risk of directly changing production reports.

36. How do you handle Power BI deployment in an enterprise?

My preferred approach is:

Developer
   ↓
Git / Version Control
   ↓
Development Workspace
   ↓
Testing
   ↓
UAT
   ↓
Production Workspace
   ↓
Power BI App

Depending on the environment, I use:

• Deployment pipelines
• Git integration
• CI/CD
• Service principals
• XMLA endpoints
• Automated deployments

37. How do you handle Power BI refresh failures?

I first determine whether the failure is:

Source issue
Power Query issue
Credential issue
Gateway issue
Model issue
Capacity issue

Then check:

1. Refresh history.
2. Error details.
3. Gateway status.
4. Data source availability.
5. Credentials.
6. Query folding.
7. Data type/schema changes.
8. Refresh duration.
9. Capacity/resource utilization.

I don’t immediately republish the report because that may hide the underlying problem.

38. What is an On-premises Data Gateway?

It allows Power BI Service to access on-premises data sources.

Architecture:

SQL Server
   ↓
On-premises Gateway
   ↓
Power BI Service
   ↓
Semantic Model
   ↓
Report

The gateway securely facilitates communication between Power BI Service and on-premises sources.

39. What is Microsoft Fabric and how does it relate to Power BI?

Microsoft Fabric is an end-to-end analytics platform.

It brings together:

• Data Engineering
• Data Factory
• Data Science
• Data Warehouse
• Real-Time Intelligence
• Power BI

A typical architecture could be:

Source Systems
      ↓
Fabric Data Factory
      ↓
OneLake
      ↓
Lakehouse
      ↓
Bronze
  ↓
Silver
  ↓
Gold
      ↓
Power BI Semantic Model
      ↓
Reports

Power BI is the analytics and visualization layer within Fabric.

40. Explain Medallion Architecture.

A common structure is:

Bronze

Raw data.

Source → Bronze

Minimal transformation.

Silver

Cleaned and standardized data.

Bronze → Silver

Examples:

• Remove duplicates
• Standardize data types
• Clean nulls

Gold

Business-ready data.

Silver → Gold

Examples:

• Fact tables
• Dimension tables
• Business calculations

Then:

Gold → Power BI Semantic Model

41. Scenario: Your dashboard takes 20 seconds to load. What do you do?

Good senior answer

I would not immediately blame Power BI.

I’d investigate the complete chain:

Source
 ↓
ETL
 ↓
Semantic Model
 ↓
DAX
 ↓
Visual
 ↓
Capacity

Then:

DAX

Use DAX Studio to examine query plans and storage/formula engine behavior.

Model

Check:

• Cardinality
• Relationships
• Column count
• Calculated columns
• Many-to-many relationships

Source

Check SQL execution time and query plans.

Visual

Remove unnecessary visuals and high-cardinality fields.

Capacity

Check whether shared/Fabric capacity is under resource pressure.

Then benchmark again after each change.

42. Scenario: Business says numbers in Power BI don’t match Excel. What do you do?

I don’t immediately assume Power BI is wrong.

I compare:

1. Data source

Are both using the same source?

2. Filters

Are the same filters applied?

3. Date logic

Are fiscal/calendar periods different?

4. Relationships

Could a relationship be causing duplication?

5. Aggregation

Could Excel be using:

SUM

while Power BI is using:

DISTINCTCOUNT

6. Business rules

Are returns, cancelled orders, or adjustments treated differently?

Then I reconcile the number step by step.

43. Scenario: A fact table has 500 million rows. How would you design the model?

I would not simply import everything into Power BI.

I’d consider:

Source
 ↓
Data Warehouse / Lakehouse
 ↓
Partitioning
 ↓
Incremental Refresh
 ↓
Aggregations where appropriate
 ↓
Optimized Semantic Model

I’d also:

• Remove unnecessary columns.
• Reduce cardinality.
• Use proper data types.
• Avoid text columns in fact tables where possible.
• Use surrogate keys.
• Create a star schema.
• Consider Direct Lake/DirectQuery depending on the Fabric architecture and requirements.

44. Scenario: Users want real-time sales data.

First I would clarify what real-time actually means.

For example:

Every 5 minutes
Every 15 minutes
Every hour
True streaming

Possible options include:

Import + scheduled refresh

or

DirectQuery

or, in Fabric environments, appropriate real-time/Direct Lake architecture.

The important point is:

I don’t choose DirectQuery simply because someone says “real-time.”

I first understand the business SLA.

45. Scenario: A user can see another country’s data despite RLS.

I would troubleshoot:

1. Confirm the user’s role assignment.
2. Test with View As.
3. Check the RLS DAX.
4. Check the security mapping table.
5. Check relationships.
6. Check filter direction.
7. Check whether the user has additional permissions.
8. Check whether the report is connected to the intended semantic model.
9. Validate USERPRINCIPALNAME().

For example:

UserAccess[Email] = USERPRINCIPALNAME()

Then verify:

User Email
     ↓
UserAccess
     ↓
DimCountry
     ↓
FactSales

46. Scenario: CEO wants one dashboard with 50 KPIs.

I would not simply create 50 cards.

I’d first understand:

• Which KPIs are decision-critical?
• Which KPIs are operational?
• Which belong to different business areas?
• Which require drill-down?

I’d design:

Executive Summary
       ↓
Business Area
       ↓
Detailed Analysis
       ↓
Transaction Details

The executive page should focus on:

• Revenue
• Profit
• Margin
• Growth
• Key operational KPIs
• Exceptions

Then drill-through provides detail.

That’s an important senior-level mindset: solving the business problem rather than just building visuals.

47. What is your approach to requirements gathering?

I typically ask:

Business

• What decision will this report support?
• Who are the users?
• What KPIs are required?
• What is the definition of each KPI?

Data

• What are the sources?
• How frequently does data refresh?
• What is the data volume?
• What is the grain?

Security

• Who can see what?
• Is RLS required?

Performance

• What is the expected response time?
• How many users?
• What refresh SLA is required?

Delivery

• Development/UAT/Production?
• Deployment process?
• Ownership and support?

48. What does “grain” mean in a fact table?

Grain defines what one row represents.

For example:

One row = One sales order line

or:

One row = One customer per day

This is extremely important.

Before creating measures or relationships, I establish the grain.

For example:

FactSales

OrderID
OrderLineID
ProductID
CustomerID
DateID
Quantity
Sales

If the grain is order line, I shouldn’t assume each row represents a complete order.

49. How do you handle duplicate data?

First identify why duplicates exist.

For example:

OrderID = 1001
Product = A

appears twice.

Possible causes:

• Duplicate source records
• Multiple status records
• Join multiplication
• Incorrect grain
• Multiple transactions

I don’t simply use DISTINCT without understanding the cause.

I’d fix the duplication at the appropriate layer.

50. How do you explain a technical issue to a business user?

Avoid saying:

“The DAX storage engine is generating an inefficient formula engine query.”

Instead say:

“The report is slow because the current calculation is processing a very large number of records every time you interact with the page. I’ve optimized the calculation and reduced the amount of data that needs to be processed.”

A senior developer should be able to communicate at both technical and business levels.