Power BI Interview Questions Chicago Employers Actually Ask

Scenario: The Chicago Operations Dashboard

You’ve joined a team that maintains an “Operations & Sales” Power BI workspace:

• Fact tables in a SQL Server data warehouse
• Dimensions for customers, products, cost centers
• A large Import model for historical analytics
• A smaller DirectQuery model for near-real-time operations
• Gateway-based refresh against on-prem SQL

Current pain points:

• Morning refresh sometimes misses its SLA and runs into business hours.
• New many-to-many relationships (customers to multiple programs, programs to multiple cost centers) are producing duplicated revenue.
• Senior stakeholders want month-to-date, quarter-to-date, and year-over-year metrics aligned with the finance calendar.
• The data engineering team is starting to ask about your SQL skills, especially around CTEs and query optimization.

These are exactly the topics Chicago employers are putting into Power BI role descriptions.

1. Report Refresh Setup: Questions You’ll Actually Get

Local contract roles often call out “strong grasp of report refresh setup.” In an interview, that turns into questions like:

• How do you design refresh schedules for different datasets in a workspace?
• What’s the difference between scheduled refresh, incremental refresh, and DirectQuery behavior?
• How do you debug a refresh failure that hits the on-premises data gateway?

Let’s stay in our scenario and answer these in a way that shows you understand how Power BI behaves today.

Scheduled vs Incremental vs DirectQuery

Key points an experienced interviewer expects you to know:

• Scheduled refresh (Import models)

  • Triggered from the Power BI service via a defined schedule.
  • Refreshes the entire dataset by default.
  • Limited by service constraints (number of daily refreshes depends on license and capacity).

• Incremental refresh (Import models)

  • Configured in Power BI Desktop on a table, then published.
  • Uses parameters (typically RangeStart and RangeEnd) and filter logic to partition data by time.
  • Only partitions in the defined refresh range are refreshed; historical partitions are left untouched.
  • Requires Premium or Fabric capacity for full capability; Pro supports incremental refresh but has limits on dataset size and capacity features.

• DirectQuery (and live connection to Analysis Services / semantic models)

  • No scheduled data refresh; queries are sent to the source at report view time.
  • There is an optional query results cache that can be refreshed, but the underlying data source is queried when needed.
  • Performance and concurrency depend heavily on the source system, model design, and query design.

In our Operations & Sales workspace, a good design is:

• Use Import + incremental refresh for large historical tables (e.g., fact sales).
• Use DirectQuery only for operational tables that truly need near-real-time (e.g., current inventory, open orders).
• Keep scheduled refresh windows early enough to avoid overlapping with business hours.

Example: Configuring Incremental Refresh for FactSales

In Power BI Desktop, you’d typically:

1. Create two parameters in Power Query (via the UI) and ensure they are datetime values, for example:

let
    RangeStart = #datetime(DateTime.LocalNow()[Year], 1, 1, 0, 0, 0)
in
    RangeStart

and similarly for RangeEnd:

let
    RangeEnd = DateTime.LocalNow()
in
    RangeEnd

(These are usually created through the Manage Parameters dialog; the key is that both are of type datetime.)

2. Apply a filter on your fact table’s date column using those parameters.
3. In the model view, configure incremental refresh for that table, specifying:
   • How many years/months to store.
   • How much recent data to refresh.

The important behavior to mention in an interview:

• Only the partitions within the “refresh” period are processed on each scheduled refresh.
• Historical partitions remain untouched, which reduces load and refresh time.
• Incremental refresh logic is applied in the service; you won’t see partitions in Power BI Desktop.

Common Refresh Failure Causes (Gateway-focused)

For a Chicago employer with on-prem SQL, a practical answer includes how you debug gateway-related failures:

• Credential issues

  • Dataset uses stored credentials; if the password changes or the account is disabled, refresh fails.
  • Fix: Update credentials in the dataset settings in the Power BI service.

• Gateway mapping problems

  • Data source in Power BI isn’t correctly mapped to a gateway data source.
  • Fix: In the dataset settings, ensure the source is assigned to the right gateway instance and data source.

• Network or firewall changes

  • Gateway can’t reach the SQL Server due to firewall rules, DNS changes, or server moves.
  • Fix: Coordinate with infrastructure to restore connectivity; verify using the gateway’s test connection.

In an interview, tie this back to SLA:

“For our Operations & Sales dataset, we schedule refresh at 4:00am with incremental refresh on the large fact tables. We monitor refresh history in the service and set alerts on failures. When we see gateway-related errors, we check credentials, gateway mapping, and connectivity before adjusting the schedule or partition logic.”

2. Many-to-Many Relationships: What Chicago Managers Care About

Postings that mention “managing many-to-many relationships” are usually hinting at two things:

• You understand how Power BI’s relationship engine works.
• You can prevent double-counting in complex business models.

In our scenario, the team added a CustomerPrograms bridge table between Customers and Programs, and a ProgramCostCenters table between Programs and Cost Centers. Revenue now appears inflated in some visuals.

How Many-to-Many Works in Power BI

Key behaviors to articulate:

• Power BI supports many-to-many relationships using shared dimensions or bridge tables.
• Relationships have:
  • Cardinality (one-to-one, one-to-many, many-to-many)
  • Direction (single or both directions)
  • Active vs inactive state
• Many-to-many can introduce ambiguity in filter propagation, especially with bi-directional relationships.

In our model:

• Customers ↔ CustomerPrograms ↔ Programs ↔ ProgramCostCenters ↔ CostCenters
• FactSales is linked to Customers and Programs.

If you set multiple bi-directional relationships, filters can traverse multiple paths and cause unexpected results.

Avoiding Double-Counting: DAX and Relationship Design

Interviewers look for answers that combine model design and DAX.

Core practices:

• Prefer single-direction relationships into fact tables when possible.
• Use bridge tables with carefully managed relationships to control filter flow.
• Use TREATAS and CALCULATE to apply filters explicitly when needed.

Example: You want total revenue by cost center, via the many-to-many chain.

Total Revenue by Cost Center =
CALCULATE (
    [Total Revenue],
    TREATAS (
        VALUES ( ProgramCostCenters[CostCenterId] ),
        FactSales[CostCenterId]
    )
)

This assumes FactSales[CostCenterId] exists and is properly populated. TREATAS applies the CostCenterId values from the bridge table as if they were filtering the fact table directly.

If your fact table doesn’t have CostCenterId, you’d instead:

• Ensure FactSales is related to Programs.
• Use relationships:
  • Programs → ProgramCostCenters (single direction)
  • ProgramCostCenters → CostCenters (single direction)

Then a simpler measure can work:

Total Revenue = SUM ( FactSales[RevenueAmount] )

and rely on filter propagation from CostCenters through ProgramCostCenters and Programs to FactSales. In the interview, you’d explicitly mention that you avoid unnecessary bi-directional relationships and validate the filter paths using the Model view and Performance Analyzer.

3. DAX Time Intelligence: Senior Role Expectations

Senior roles in Chicago often call out “DAX time intelligence” explicitly. The questions are usually:

• How do you implement month-to-date, quarter-to-date, and year-to-date measures?
• How do you handle a custom fiscal calendar?
• How do you compute year-over-year growth safely?

In our scenario, finance uses a fiscal year starting in a month that may differ from January. You can’t rely only on standard calendar functions.

Core Time Intelligence Measures

Assume a properly marked Date table (Date table marked as a Date table in the model, with continuous dates).

Basic measures:

Total Revenue = SUM ( FactSales[RevenueAmount] )
MTD Revenue =
CALCULATE (
[Total Revenue],
DATESMTD ( 'Date'[Date] )
)
QTD Revenue =
CALCULATE (
[Total Revenue],
DATESQTD ( 'Date'[Date] )
)
YTD Revenue =
CALCULATE (
[Total Revenue],
DATESYTD ( 'Date'[Date] )
)

These use the calendar year. To show you understand DAX behavior:

• CALCULATE modifies filter context.
• DATESMTD, DATESQTD, DATESYTD return table expressions of dates used as filters.

Fiscal Calendar Handling

For a fiscal year that doesn’t start in January, you can use DATESYTD with a year-end parameter or build custom logic.

Example: Fiscal year ends on June 30 (adjust this to your actual scenario; the pattern is what matters).

Fiscal YTD Revenue =
CALCULATE (
    [Total Revenue],
    DATESYTD ( 'Date'[Date], "06-30" )
)

Key points:

• The second argument to DATESYTD is the fiscal year-end date (month and day) as a string.
• The function adjusts the year grouping accordingly.

For more complex calendars (e.g., 4-4-5), you might:

• Add fiscal year and fiscal period columns to the Date table.
• Use those columns in custom time intelligence logic instead of relying solely on built-in functions.

Year-over-Year (YoY) Measures

Standard YoY revenue:

Revenue Last Year =
CALCULATE (
    [Total Revenue],
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
Revenue YoY Change =
[Total Revenue] - [Revenue Last Year]
Revenue YoY % =
DIVIDE ( [Revenue YoY Change], [Revenue Last Year] )

In an interview, mention:

• SAMEPERIODLASTYEAR requires a contiguous Date table and consistent granularity.
• DIVIDE is preferred over / because it handles divide-by-zero gracefully.

Then tie it back to the scenario: “We use these measures on the Operations & Sales dashboard to align with the fiscal calendar, and we validate them against finance’s numbers before publishing.”

4. SQL CTEs: What Power BI Teams Actually Want to Hear

Senior postings in Chicago often mention “SQL CTEs” alongside Power BI. They’re not looking for database admin skills; they want to know you can:

• Read and debug SQL queries that Power BI or the warehouse team uses.
• Write CTE-based queries that are clear and maintainable.
• Understand how query design affects DirectQuery performance.

In our scenario, the data warehouse team exposes views that Power BI uses in DirectQuery. You might be asked to optimize a query that joins multiple tables and filters by date.

CTE Basics in a Power BI Context

A Common Table Expression (CTE) is a named subquery defined with WITH that can be referenced in a subsequent SELECT, INSERT, UPDATE, or DELETE statement.

Example: A CTE that prepares daily revenue, then aggregates to month level.

WITH DailyRevenue AS (
    SELECT
        d.Date,
        s.CustomerId,
        s.ProgramId,
        SUM(s.RevenueAmount) AS Revenue
    FROM FactSales s
    INNER JOIN DimDate d
        ON s.DateKey = d.DateKey
    WHERE d.Date >= DATEADD(DAY, -30, CAST(GETDATE() AS date))
    GROUP BY d.Date, s.CustomerId, s.ProgramId
)
SELECT
    YEAR(Date) AS CalendarYear,
    MONTH(Date) AS CalendarMonth,
    SUM(Revenue) AS MonthlyRevenue
FROM DailyRevenue
GROUP BY YEAR(Date), MONTH(Date);

Points to highlight:

• The CTE improves readability by separating daily aggregation from monthly aggregation.
• The date filter limits rows, which matters for DirectQuery performance.
• This query can be exposed as a view or used as the basis for a Power BI table.

CTEs and DirectQuery Performance

When Power BI uses DirectQuery against SQL:

• Each visual generates SQL queries against the source.
• Complex CTEs can be fine if they’re well-indexed and avoid unnecessary work.
• Poorly constrained CTEs (no date filters, no proper joins) can cause slow visuals.

In an interview, connect the dots:

“For our DirectQuery tables, we work with the data engineering team to ensure views are based on CTEs that pre-aggregate where appropriate and apply date filters. We then validate performance using Power BI’s Performance Analyzer and SQL Server’s query plans.”

5. Putting It Together for Chicago-Style Interview Questions

Hiring managers in Chicago aren’t just checking buzzwords. They’re probing whether you can:

• Design refresh strategies that meet SLAs on on-prem data.
• Model many-to-many relationships without corrupting business logic.
• Implement reliable time intelligence aligned with finance.
• Read and write SQL CTEs that keep DirectQuery reports responsive.

In our Operations & Sales scenario, a strong candidate would describe:

• Incremental refresh on large fact tables, scheduled before business hours, monitored via refresh history.
• A relationship design that uses bridge tables, single-direction filters into fact tables, and DAX tools like TREATAS when necessary.
• A marked Date table with calendar and fiscal attributes, and a set of DAX measures for MTD/QTD/YTD and YoY that finance can reconcile.
• Collaboration with the data warehouse team on CTE-based views that balance clarity and performance for Power BI.

One Practical Takeaway

Before your next Power BI interview in Chicago, take one of your real reports and:

• Review its refresh setup, relationship design, time intelligence measures, and underlying SQL views.

Then be ready to walk through that report end-to-end; the same story that keeps your dashboard alive at 6am is exactly what local employers want to hear when they ask their Power BI interview questions.