Power BI interview questions

What a strong answer covers

• Explains Power Query Editor as Power BI's ETL (Extract, Transform, Load) tool.
• Details its functions: connecting to diverse data sources, cleaning data (handling nulls, errors), shaping data (renaming, removing columns, changing data types).
• Mentions advanced transformations like merging, appending, pivoting/unpivoting data.
• Highlights its importance in ensuring data quality, consistency, and optimal structure for the data model and subsequent analysis.

Where people lose the point

• ×Only mentioning data connection without emphasizing transformation capabilities.
• ×Confusing Power Query's role with DAX for calculations.
• ×Not explaining how it impacts the quality and performance of the final report.

What a strong answer covers

• Calculated Column: Computed row-by-row during data refresh, stored in the model, consumes memory, evaluated in row context, best for static attributes or categorizations.
• Measure: Computed on-the-fly based on current filter context, not stored, consumes less memory (during storage), evaluated in filter context, best for aggregations and dynamic calculations.
• Scenario for Calculated Column: Creating a 'Full Name' from 'First Name' and 'Last Name', or a 'Fiscal Year' from a 'Date' column.
• Scenario for Measure: Calculating 'Total Sales' (SUM of Sales Amount) or 'Average Order Value' (Total Sales / Total Orders) which changes based on filters.

Where people lose the point

• ×Incorrectly stating when they are calculated (e.g., measure calculated at refresh).
• ×Not mentioning the impact on memory/storage for calculated columns.
• ×Confusing row context and filter context in their explanations.

What a strong answer covers

• Purpose: To integrate data from different tables, allowing filters applied to one table to propagate and filter related tables, enabling accurate aggregations and cross-table analysis.
• Explains how relationships facilitate data slicing and dicing across dimensions.
• Describes cardinality: One-to-Many (most common, e.g., Customer to Sales), Many-to-One (inverse of one-to-many), One-to-One (rare, e.g., Customer to CustomerDetails), Many-to-Many (requires bridge table).
• Mentions cross-filter direction (single or both) and its impact on filter propagation.

Where people lose the point

• ×Only mentioning joining tables without explaining filter propagation.
• ×Incorrectly describing the commonality or implications of different cardinality types.
• ×Not discussing the importance of relationships for analytical capabilities.

What a strong answer covers

• Definition: A data modeling approach with a central 'fact' table (containing measures/quantitative data) surrounded by 'dimension' tables (containing descriptive attributes).
• Characteristics: Direct one-to-many relationships between the fact table and each dimension table, dimensions are typically denormalized.
• Benefits for Power BI: Optimized query performance due to simpler joins, easier to understand and navigate for report developers, simplified DAX calculations, reduced data redundancy, and improved filter propagation.
• Contrasts with snowflake schema (optional, but shows deeper understanding).

Where people lose the point

• ×Confusing star schema with a single, wide denormalized table.
• ×Not clearly explaining the distinction between fact and dimension tables.
• ×Failing to articulate the performance and usability benefits for Power BI specifically.

What a strong answer covers

• Explains `CALCULATE`'s primary role: to evaluate an expression in a modified filter context.
• Details how it takes an expression as its first argument and then a series of filter arguments.
• Describes how `CALCULATE` first removes existing filters on columns specified in its filter arguments, then applies new filters, and finally evaluates the expression.
• Highlights its power in enabling complex calculations like 'Sales for Previous Year', 'Sales for a specific region', or 'Percentage of Total' by manipulating filter context.

Where people lose the point

• ×Treating `CALCULATE` as a simple aggregation function without explaining filter context modification.
• ×Not understanding that `CALCULATE` implicitly performs context transition when used with row context.
• ×Failing to provide concrete examples of how it modifies filters.

What a strong answer covers

• Row Context: The context in which a formula is evaluated row by row. It's typically created by iterator functions (e.g., `SUMX`, `AVERAGEX`) or when creating calculated columns.
• Example for Row Context: A calculated column `[Line Total] = [Quantity] * [Unit Price]` evaluates for each row individually.
• Filter Context: The set of filters applied to a calculation by visuals, slicers, report filters, or other DAX expressions. It determines the subset of data being evaluated.
• Example for Filter Context: A measure `[Total Sales] = SUM(Sales[Amount])` will show different values depending on the selected year in a slicer or a filter applied to a chart.

Where people lose the point

• ×Mixing up the definitions or examples of the two contexts.
• ×Not clearly explaining how each context influences the calculation's outcome.
• ×Failing to mention how `CALCULATE` interacts with and modifies filter context.

What a strong answer covers

• Clarity and Conciseness: Focus on key metrics, avoid clutter, use clear titles and labels, ensure visuals are easy to interpret.
• Visual Selection: Choose appropriate chart types for the data (e.g., line for trends, bar for comparisons, pie for parts-to-whole sparingly).
• Interactivity: Implement slicers, drill-through, and bookmarks to allow users to explore data dynamically and personalize their view.
• Performance Optimization: Minimize visuals, use efficient DAX, optimize data model, consider incremental refresh for large datasets.
• User Experience (UX): Consistent branding, intuitive navigation, logical flow of information, accessibility considerations (color contrast, text size).

Where people lose the point

• ×Focusing solely on aesthetics without considering functionality or performance.
• ×Not mentioning interactivity as a key component of user-friendly reports.
• ×Ignoring the importance of data model efficiency in report performance.

What a strong answer covers

• Method: Introduce a 'bridge table' (or junction table) between the two tables that have a many-to-many relationship. This bridge table should have unique keys from both tables, converting the many-to-many into two one-to-many relationships.
• Example: A 'Student' can take many 'Courses', and a 'Course' can have many 'Students'. A 'StudentCourse' bridge table would link them.
• Implications: Can lead to ambiguous filter propagation if not handled correctly, potential performance overhead with large bridge tables, requires careful design to ensure correct aggregations.
• Discusses bidirectional filtering as an alternative (less recommended) and its risks.

Where people lose the point

• ×Attempting to create a direct many-to-many relationship without a bridge table (which Power BI handles poorly).
• ×Not understanding the filter propagation issues that arise from many-to-many relationships.
• ×Failing to explain the purpose and structure of a bridge table.

What a strong answer covers

• Purpose: A secure bridge that connects Power BI Service (cloud) to on-premises data sources (e.g., SQL Server, Excel files on a network drive, SharePoint on-prem).
• Functionality: Enables scheduled data refreshes and DirectQuery connections for datasets that reside behind a firewall.
• Necessity: Required whenever your Power BI dataset in the cloud needs to access data that is not directly accessible from the internet.
• Types: On-premises data gateway (standard mode for multiple users/sources) and personal mode (for single user/source).

Where people lose the point

• ×Confusing it with a VPN or other network security tools.
• ×Not understanding that it's specifically for connecting cloud services to on-premises data.
• ×Failing to mention its role in both scheduled refresh and DirectQuery.

What a strong answer covers

• Diagnosis Tools: Use Power BI Desktop's Performance Analyzer to identify slow visuals and DAX queries. Utilize DAX Studio for detailed query analysis and server timings.
• Data Model Optimization: Implement a star schema, remove unnecessary columns/rows in Power Query, optimize data types, ensure correct relationships and cardinality, consider aggregation tables for large datasets.
• DAX Optimization: Refine complex DAX measures, avoid calculated columns where measures suffice, use efficient functions, understand context transition.
• Visual Optimization: Reduce the number of visuals on a page, avoid complex custom visuals, use filters to limit data displayed, consider report page tooltips instead of many small visuals.
• Data Refresh Strategy: Implement incremental refresh for large datasets, optimize source queries, ensure efficient data gateway setup.

Where people lose the point

• ×Only focusing on visual design without addressing the underlying data model or DAX.
• ×Not mentioning specific tools like Performance Analyzer or DAX Studio.
• ×Suggesting solutions without a clear diagnostic process.

What a strong answer covers

• Implementation Steps: Define roles in Power BI Desktop (Modeling tab > Manage Roles), write DAX filter expressions for each role to restrict rows in specific tables.
• Deployment: Publish the report to Power BI Service, then assign users or security groups to the defined roles within the Power BI Service workspace.
• Testing: Test RLS in Power BI Desktop using 'View as roles' and verify in the Power BI Service.
• Considerations: RLS only filters data rows, not columns or visuals (Object-Level Security for that); performance impact with complex DAX filters; ensuring proper user/group management in Azure AD; RLS does not apply to data designers/admins.

Where people lose the point

• ×Confusing RLS with Object-Level Security (OLS) or other security mechanisms.
• ×Not explaining the two-step process (defining in Desktop, assigning in Service).
• ×Failing to mention the importance of testing RLS thoroughly.

What a strong answer covers

• `SUM()`: An aggregation function that sums all values in a single column within the current filter context. It does not iterate row by row.
• `SUMX()`: An iterator function that takes a table expression and an expression to evaluate for each row of that table, then sums the results. It creates a row context for its evaluation.
• Key Difference: `SUM` operates on a column in filter context; `SUMX` iterates over rows of a table, creating a row context for each row's calculation before summing.
• Scenario for `SUMX()`: Calculating 'Total Sales Amount' where `SalesAmount` is not a pre-calculated column but needs to be derived from `Quantity * Price` for each individual transaction row, then summed up. `SUMX(Sales, Sales[Quantity] * Sales[Price])`.

Where people lose the point

• ×Using `SUM` and `SUMX` interchangeably without understanding their fundamental difference in context.
• ×Not clearly explaining `SUMX`'s iterative nature and the creation of row context.
• ×Providing a scenario for `SUMX` that could be easily solved with `SUM`.

What a strong answer covers

• Definition: A feature that allows Power BI to refresh only a subset of data (new or updated data) rather than the entire dataset during scheduled refreshes.
• Benefits: Significantly reduces refresh times, lowers resource consumption on both Power BI and source systems, improves report reliability by minimizing data transfer.
• Prerequisites: Requires Power BI Premium, Premium Per User (PPU), or Power BI Pro license (with specific limitations for Pro). The data source must support query folding. The table must have a date/time column used for filtering.
• Implementation: Configured in Power BI Desktop using `RangeStart` and `RangeEnd` parameters in Power Query, then set up in the dataset settings in Power BI Service.

Where people lose the point

• ×Thinking incremental refresh is automatic without configuration.
• ×Not knowing the licensing requirements (e.g., assuming it's available for all Pro users without limitations).
• ×Failing to mention the need for a date/time column and query folding.

What a strong answer covers

• Identify Fact Table: 'Sales' (containing transaction details like OrderID, ProductID, CustomerID, Date, Quantity, Price, SalesAmount).
• Identify Dimension Tables: 'Customer' (CustomerID, Name, City, State, AgeGroup), 'Product' (ProductID, Name, Category, SubCategory), 'Date' (DateKey, Year, Month, Day, Weekday).
• Define Relationships: One-to-many relationships from 'Customer' to 'Sales' (on CustomerID), 'Product' to 'Sales' (on ProductID), and 'Date' to 'Sales' (on Date).
• Cardinality and Cross-Filter Direction: All relationships should be one-to-many with single cross-filter direction from dimension to fact.
• Star Schema Principle: Emphasize adherence to star schema for performance and ease of analysis.

Where people lose the point

• ×Creating a single, wide denormalized table instead of separate fact and dimension tables.
• ×Incorrectly defining cardinality or cross-filter direction for relationships.
• ×Missing a dedicated 'Date' dimension table, which is crucial for time-based analysis.