Hard Difficulty Questions

Create an aggregation table on the dimension

Why wrong: Creating an aggregation table on the dimension is not supported in DirectQuery mode for this scenario; aggregations require Import or Composite models.

Change the storage mode of the dimension table to Dual

Why wrong: Changing the storage mode of the dimension table to Dual is not applicable when the source is DirectQuery; Dual mode is used for tables that are both Import and DirectQuery.

Reduce the cardinality of the slicer field by grouping values

Why wrong: Reducing the cardinality by grouping values might reduce accuracy and does not improve the underlying query performance; it changes the data granularity.

The CALCULATE function is misused and should be replaced with SUMX

Why wrong: The CALCULATE syntax is actually valid; replacing it with SUMX would not change the outcome because the problem lies in the filter argument, not the aggregation function. Both CALCULATE and SUMX would operate under the same modified filter context, and the logical error of using ALL(Date) to strip the month filter remains. The measure returns a constant because every month sees the same unfiltered Date table, not because of a CALCULATE versus SUMX misuse.

The Date table is not marked as a date table

Why wrong: Marking a table as a date table is only required for time intelligence functions like TOTALYTD or DATESYTD, and it also enables automatic hierarchy support in newer Power BI versions. This calculation does not rely on any time intelligence function; it's a simple aggregation with a custom filter. Even an unmarked date table can be used in CALCULATE, so the absence of this setting does not explain the constant results; the filter logic does.

The relationship between Sales and Date is inactive

Why wrong: If the relationship between Sales and Date were inactive, the model would still have an active relationship elsewhere or the measure would need USERELATIONSHIP to explicitly activate it. In that scenario, the measure would either return BLANK or the values would vary because the month context would not cascade to Sales at all, producing a different symptom. The consistent constant value observed indicates a context removal problem, not an inactive relationship, which would typically cause missing or unrelated aggregations.

Use incremental refresh to import only recent data, and use DirectQuery for older data.

Why wrong: This hybrid approach (importing recent data via incremental refresh and streaming older data via DirectQuery) cuts refresh time, but it does little for query performance: any report that needs historical perspective sends large SQL queries across the entire older transaction volume in Synapse. The imported recent partition is small, but the DirectQuery older partition still forces the user, and Synapse, to scan billions of rows on almost every interaction. It also creates an awkward boundary at the refresh date and doubles the semantic complexity (e.g., partition cutoffs) without solving the core problem of excessive data volume.

Import only the necessary columns and use Power BI aggregations to pre-aggregate.

Why wrong: Power BI aggregations are stored in the model and can be used for high-level visuals, but the underlying transaction table must still be imported—or accessed via DirectQuery—in full granularity. The 'import only necessary columns' step reduces column width, not row count, so a 5-billion-row table still consumes massive memory and takes a long time to refresh. In contrast, pre-aggregating upstream in Synapse lets the database engine compress billions of rows into a few thousand before they ever reach Power BI, eliminating the need to store or query the raw detail in the model.

Use DirectQuery on the full Transactions table and rely on the Synapse query optimizer.

Why wrong: DirectQuery over the complete Transactions table sends every visual query to Synapse as a huge scan, and even with columnstore indexes and query optimizer improvements, latency from network round-trips and row exfiltration will overwhelm the report. Optimizer settings help with query plans, not with fundamental volume: billion-row scans are still slow, and Power BI's query timeouts and default MAX_SELECTED_ROWS limits make visuals brittle. This puts the load on Synapse for every dashboard interaction and offers no cached aggregate, so page loads will frequently time out or return inconsistent results.

To avoid using an on-premises data gateway

Why wrong: Dataflows do not eliminate the need for an on-premises data gateway. A gateway is still required whenever a dataflow queries source systems residing on-premises, such as SQL Server, since the dataflow engine runs in the Power BI service and cannot reach private networks without a gateway bridge. The presence of a dataflow merely centralizes ETL; it does not change the connectivity requirement.

To achieve real-time data refresh

Why wrong: Dataflows are not designed for real-time refresh; they follow a scheduled, batch-based ingestion model where you manually or programmatically trigger refresh. Because Power Query expressions are executed to produce a snapshot of the tables, data is refreshed at intervals (e.g., daily) rather than pushed continuously. To achieve real-time analytics, you would instead use DirectQuery or push datasets, not dataflows.

To automatically enforce data lineage

Why wrong: While Power BI offers a lineage view that shows how dataflows flow into datasets, reports, and dashboards, dataflows do not automatically enforce data lineage. Lineage is descriptive metadata—it displays relationships but does not validate access or prevent a user from pointing to the wrong data source. Governance controls such as data classification, row-level security, and sensitivity labels must be configured manually, and they are not intrinsic to creating a dataflow.

Merge tables as early as possible in the query to combine data.

Why wrong: Merging early may cause large data volumes to be processed; delay merging if possible.

Keep all columns in the table to avoid missing data.

Why wrong: Removing unnecessary columns reduces memory and processing time.

Split columns by delimiter to normalize data.

Why wrong: Splitting columns increases the number of columns and processing steps, potentially reducing performance.

Import the entire table and apply a date filter in Power Query Editor.

Why wrong: Importing the full table and then applying a date range filter in Power Query Editor does not reduce the amount of data pulled from Azure SQL Database; the entire table is materialized into the Power BI data model before the filter is evaluated. Because the requirement is to minimize load time, transferring millions of rows unconditionally defeats the purpose and inflates both loading time and compression storage. In this workflow the filter arrives too late to shrink the dataset at the source.

Use DirectQuery mode and apply a date filter in the report.

Why wrong: DirectQuery mode avoids importing a local copy by sending queries to Azure SQL Database at report/render time, so there is no import load at all; however, the stated goal is to minimize the load time for an import-mode solution, and DirectQuery does not produce the compact imported dataset the user asked for. Every report interaction requires a round-trip to SQL, which can make report performance dependent on source database throughput and indexing. Merely adding a date filter in the report does not change the fact that the data remains fully external and query performance is paid for live, not during a refresh/import.

Disable the 'Enable load' option for the SalesTable

Why wrong: Disabling load would not load any data, which is not desired.

Use CALCULATE in DAX to filter

Why wrong: DAX is not used in Power Query; it's for measures.

Add a 'Table.Buffer' step after the filter

Why wrong: Buffer does not push filter to source; it caches after loading.

Create a separate table for managers

Why wrong: Creating a separate table for managers is unnecessary and counterproductive for a parent-child hierarchy. The self-referencing relationship already links each employee to their manager through the ManagerID column, so a separate managers table would duplicate data and complicate the model. It would also break the direct traversal path that PATH() and related functions depend on, forcing extra lookups and joins that degrade performance and add maintenance overhead.

Set cross filter direction to Both on the relationship

Why wrong: Setting the cross filter direction to Both on the self-referencing relationship is not needed and is generally a bad practice. A single-direction filter (from EmployeeID to ManagerID) is sufficient to propagate the hierarchy, since parent-child traversal is implicit through the relationship. Bidirectional filtering can create ambiguous filter contexts, increase query complexity, and cause performance issues, especially in large or complex models, so the default single-direction should be retained.

Split a large fact table into multiple smaller fact tables.

Why wrong: This can complicate the model and may not improve performance.

Use DirectQuery mode instead of Import mode to reduce data load time.

Why wrong: DirectQuery does not load data, but it is not always a performance best practice.

The relationship is configured as many-to-many.

Why wrong: Many-to-many cardinality does not prevent Power BI from resolving a filter from Sales to Regions; if a relationship is active, even a many-to-many configuration will still allow the report to display RegionName by cross-filtering between the tables. The cardinality only defines how matching rows are handled (e.g., via a bridge table or direct many-to-many), but it does not disable the relationship itself. The symptom of showing RegionID instead of RegionName points to the relationship being inactive or absent, not to its cardinality, so the many-to-many setting is irrelevant to this issue.

The relationship direction is set to Both.

Why wrong: Setting cross-filter direction to Both controls whether filters propagate bidirectionally across the relationship, but it has no effect on whether the relationship is active. Even a single-direction filter from Regions to Sales would be sufficient to allow the report to look up and display RegionName when the relationship is active. Since the described problem is a missing or inactive relationship, changing the direction to Both cannot activate it; bi-directional filtering only applies to an existing active relationship and cannot compensate for one that is inactive.

The RegionID column in the Sales table is hidden.

Why wrong: Hiding a column in the Sales table only removes it from the field list and from visual-level drag-and-drop interactions; it does not affect the underlying data model or the relationship metadata. Relationships in Power BI are defined at the model level and continue to function normally even if a column is hidden, so hiding RegionID would not prevent Power BI from using it as a foreign key. The visible display of RegionID instead of RegionName is due to the lack of an active relationship that would enable a lookup to the Regions table, not because the column is hidden.

All Sales and Targets data because John is a manager.

Why wrong: Being a manager does not grant any exemption from row-level security; Power BI evaluates RLS predicates for every authenticated user regardless of their role, title, or other permissions. The filters defined in the role are applied as mandatory predicates on each table, so John can never see rows outside those fitted constraints. Therefore, the correct result cannot be the entire Sales and Targets data simply because John holds a managerial position.

Sales rows where Region is 'West' OR Manager is 'john@contoso.com'; Targets rows where Region is 'West'.

Why wrong: The Sales filter is not an OR condition; RLS predicates on the same table are implicitly combined with AND, meaning a row must be in the West region and be owned by John at the same time. An OR would broaden the visible set to every West region row plus every John-owned row, which would violate the security intent of the West-only restriction. Although the Targets table remains correctly filtered by Region, the Sales side is wrong because OR expands visibility beyond the intended intersection.

Only Sales rows where Manager is 'john@contoso.com'; no Targets restriction.

Why wrong: This option omits the additional RLS condition on Sales that restricts Region to 'West'; without it, John would see Sales rows outside the West region as long as he is the manager, which violates the defined security policy. Additionally, RLS rules on the Targets table are independent and still apply, so Targets cannot be left unrestricted. Therefore, John is limited to Sales rows satisfying both the Region and Manager predicates, plus Targets rows satisfying the Region predicate only.

Use DirectQuery storage mode for all tables to ensure real-time data and aggregate queries at the source.

Why wrong: Using DirectQuery for all tables over a 500-million-row Sales table is a performance anti-pattern because every visual interaction sends a query to the source database, and even well-indexed aggregate queries frequently exceed 5 seconds due to network latency and source engine load. Additionally, DirectQuery limits DAX time-intelligence functions and requires the underlying source to stay available for every user interaction, making it a poor fit for a model that also needs a calendar hierarchy and responsive reporting.

Use a composite model: Import for dimension tables and DirectQuery for Sales table to balance freshness and performance.

Why wrong: A composite model that imports dimension tables but keeps Sales in DirectQuery still forces every aggregation on the fact table to hit the source, so slicer selections and page-level calculations such as year-to-date totals will be slow with 500M rows. The composite approach also adds overhead by maintaining two storage engines and can introduce ambiguity in relationships, especially when the DirectQuery table participates in measures that reference imported columns.

Use Import mode but do not create a date table; instead use the DateKey column from Sales for time intelligence.

Why wrong: Using the DateKey column from the Sales table as a pseudo-date table will break DAX time-intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR, which require a separate contiguous date table marked with a 'Date table' setting. It also prevents automatic hierarchy creation (Year–Quarter–Month) and limits the ability to filter by calendar days that lack transaction records, so the report would fail the explicit date hierarchy requirement even though import mode is used.

The Year column is of type datetime, but the filter is an integer.

Why wrong: A datetime column is inherently numeric at the database level, so comparing it to an integer literal is a valid, foldable relational predicate. Power Query's M engine also supports implicit coercion between numeric and datetime types in an 'each' expression, meaning the column type would not block query folding. The real reason folding fails is that the Year column is stored as text, which creates a true type-incompatibility with the numeric comparison.

The query uses a native SQL query instead of a table reference.

Why wrong: The M expression explicitly calls Table.SelectRows on a table reference (presumably a parameter or prior step), not on a Value.NativeQuery or a direct SQL string. Query folding is designed to work with table references and standard M functions like SelectRows; using a native SQL query would bypass the Power Query engine entirely and is not what the code shows. Therefore, this option misidentifies the structure of the query and cannot explain the missing folding.

The data source is using DirectQuery mode, which prevents folding.

Why wrong: DirectQuery mode does not prevent query folding; in fact, DirectQuery requires folding because the data is not imported, so every transformation must be translated into source queries. If a step cannot be folded in DirectQuery, Power Query will either attempt to load the data locally or throw an error, but the mode itself is not a barrier. The lack of folding in this scenario is due to the data source column type, not the connection mode.

Use DirectQuery for all data sources and create views in Azure SQL to transform the SharePoint and Dataverse data. Use Power Query to combine SharePoint files in a view.

Why wrong: DirectQuery is not universally supported: Azure SQL supports it, but SharePoint and Dataverse do not. SharePoint lacks DirectQuery entirely, so using Power Query to combine files into a view in Azure SQL would necessitate moving data to a separate source, defeating the purpose of DirectQuery's zero-copy querying. Even if Dataverse could be queried directly, this approach fails to address the requirement for a composite model that balances near real-time with historical data, and it introduces needless complexity without a functional path for SharePoint.

Import all data into Power BI using Import mode. Schedule refreshes every 15 minutes for the current month and daily for historical data.

Why wrong: Import mode stores data in the Power BI in-memory engine, so it cannot provide true near real-time access to the current month's sales. Scheduled refreshes are constrained by capacity and refresh duration; 15-minute intervals are not supported for Import mode in most Shared or even Premium capacities, and each refresh would re-query the entire Azure SQL dataset, making it slow and resource-intensive. This approach also lacks the incremental refresh feature needed to efficiently manage historical data, and it fails to meet the business's explicit near real-time requirement for current-month data.

Use Azure Data Factory to copy all data to Azure SQL Database, then connect Power BI using DirectQuery.

Why wrong: Azure Data Factory (ADF) can copy data from all sources into Azure SQL, and DirectQuery would then provide a consistent query layer, but this introduces batch latency: ADF pipelines run on a schedule, not continuously, so the SQL database would be stale between runs, failing the near real-time requirement. Additionally, copying all data defeats the purpose of DirectQuery by creating a full duplicate in Azure SQL, adding storage cost and copy time. While ADF could integrate SharePoint files, the extra ETL layer and schedule-based updates make this solution slower and more complex than a composite model, and it does not leverage Power BI's native incremental refresh or mixed storage capabilities.

Use composite keys in relationships for better performance

Why wrong: Composite keys in relationships require multiple columns to join tables, which increases the size of the relationship index and adds complexity to query resolution. This often degrades performance because Power BI must evaluate and match every key column on each row, and any change to one of the key columns forces a re-evaluation of the relationship. In contrast, a single-column surrogate key provides a smaller, more efficient join key and avoids the risk of ambiguous matches.

Enable bidirectional cross-filtering by default

Why wrong: Enabling bidirectional cross-filtering by default is a common anti-pattern because it lets a filter on one table flow through to another table and then back again, which can create multiple, conflicting filter paths. This ambiguity often leads to inaccurate results, especially when multiple fact tables or many-to-many relationships are involved, and it can significantly slow down query execution. You should enable this feature only when a specific business requirement demands it, typically combined with CALCULATETABLE or CROSSFILTER functions to control the flow.

Use many-to-many relationships directly without bridge tables.

Why wrong: Creating a direct many-to-many relationship without a bridge table forces the engine to resolve ambiguous joins between two non-unique key columns, which can cause row inflation and unpredictable totals in visuals. In Power BI, a many-to-many cardinality is only supported in specific modeling scenarios (for example, when both tables have non-unique keys but a bridge table is absent); it should still be avoided because the query plan cannot guarantee correct aggregate results. The recommended pattern is to introduce a bridge table that splits the many-to-many into two one-to-many relationships, preserving deterministic filter semantics and making performance easier to control.

Use calculated columns instead of measures for aggregations.

Why wrong: Calculated columns are evaluated at data refresh time and stored in memory, increasing model size and slowing refresh, whereas measures are evaluated at query time and do not consume storage. This fails the performance goal because aggregations in measures avoid unnecessary memory overhead and recalculate only when needed. It is tempting because calculated columns are useful for row-level static categorisation or creating relationships, where pre-computed values are required before any user interaction.

Use a custom visual that combines both views in one chart

Why wrong: A custom visual that blends category and trend views into a single chart still requires the full underlying dataset to be loaded into the visual's data view at render time. Because custom visuals often perform client-side aggregation and lack the built-in query optimizations of native Power BI visuals, they can actually increase memory and CPU overhead. This approach does not defer or reduce the initial data load, so it does not address the goal of minimizing report startup time.

Use bookmarks to switch between category view and daily trend view

Why wrong: Bookmarks only alter visual visibility and page navigation after all visuals have already been loaded into memory; they have no effect on initial query execution. When a report page contains both the category and trend visuals, Power BI still processes every visual's data at open time, regardless of whether a bookmark hides it later. Thus, bookmark toggling does not reduce the data volume loaded and cannot substitute for true deferred loading such as a drillthrough page.

Place a stacked column chart showing all categories and a line chart for trend on the same page

Why wrong: Placing a stacked column chart and a line chart on the same report page forces Power BI to instantiate and render both visuals during the initial page load. Even though they may share a common data source, each visual independently requests and holds its aggregation of the dataset, doubling client-side rendering work and increasing the memory footprint. This strategy worsens, rather than improves, the initial load performance for the sales team.

The incremental refresh policy is applied at the partition level instead of the table level

Why wrong: The incremental refresh policy is configured as a table-level property, not a partition-level property. Power BI automatically generates and manages date-based partitions from the policy's RangeStart and RangeEnd parameters, and you do not manually apply a policy to a partition. Saying the policy is applied at the partition level misidentifies where the configuration lives; the partitions are just the result of that table-level definition.

The incrementalPeriodsOffset should be set to a negative value to include historical data

Why wrong: Setting IncrementalPeriodsOffset to a negative value is not a way to include historical data, and negative offset values are not supported in Power BI. The offset parameter shifts the entire refresh window backward by a non-negative number of periods to accommodate data latency, but it does not extend the window to retain or refresh older partitions. To include more history, you must increase IncrementalPeriods or explicitly set RangeStart to an earlier date; the offset alone cannot preserve data beyond the defined window.

The refresh policy automatically removes data older than the incremental period

Why wrong: An incremental refresh policy does not automatically remove or purge data older than the incremental period. It only tells Power BI which partitions need refreshing, and any data outside the refresh window remains intact in the model. Deleting historical rows would require either filtering the source query to exclude them, manually dropping partitions, or building a separate cleanup process. Believing the policy deletes old data is a common error that leads to unexpected storage growth.

Enable Power BI activity logs and query Log Analytics for shareable links.

Why wrong: Power BI activity logs record user actions such as CreateShareableLink or ViewReport, but they do not capture the actual shareable link URLs, their creation timestamps, or a comprehensive inventory of currently active links. Querying Log Analytics against these logs would only return audit events for link-related operations that happened after logging was enabled, giving no way to enumerate existing links or filter by CreatedDate to identify and revoke stale ones. Thus, this approach cannot serve as a reliable mechanism for discovering and revoking links across the enterprise.

Use Microsoft Graph API to audit and manage Power BI shareable links.

Why wrong: Microsoft Graph is the unified API for Microsoft 365 services, but its permissions and resource models do not expose Power BI shareable links (sometimes called 'publish to web' or 'secure embed' links) for management. While Graph can handle some Power BI artifacts like workspaces and reports via delegated permissions, it does not provide endpoints to list, filter, or revoke individual shareable URLs that grant anonymous access or signed-in user access. Any attempt to manage these links through Graph would fail because the link objects and their revocation actions are only available through the Power BI REST API and its corresponding admin cmdlets.

Use the Power BI admin portal to manually export shareable links.

Why wrong: The Power BI admin portal provides a UI for viewing and managing shareable links, but it offers no bulk export feature to extract a list of all links (with their creation dates) across the entire tenant. An admin would have to navigate through each workspace and report individually, which is impractical when there are thousands of workspaces and even more reports with multiple links. Moreover, the portal does not expose a way to programmatically filter links by CreatedDate or batch-revoke them, so manual export would still require a separate manual deletion step for every link, making it error-prone and impossible to enforce a consistent retention policy at scale.

Switch to a table visual.

Why wrong: Switching to a table visual does not address the underlying performance problem—it only changes the layout. A table visual lacks the native hierarchy drill-down capability of a matrix, so you would still have to include every row for the fully expanded dataset, which can actually increase the amount of data loaded into memory and impair rendering. The most effective way to reduce the initial data load and improve rendering time is to use a drill-down hierarchy that defers loading lower levels until the user explicitly expands them.

Add more filters to the visual.

Why wrong: Adding more filters to the visual can reduce the visible data by narrowing the scope, but it doesn't resolve the structural inefficiency of expanding all levels at once. Additional filters may trigger extra query execution and could inadvertently exclude relevant data, altering the executive's view. The core problem is that the visual is requesting all expanded rows in a single query; a drill-down hierarchy is a better optimization because it loads only the top-level rows initially and fetches lower levels on demand.

Increase the number of measures in the matrix.

Why wrong: Increasing the number of measures in the matrix would worsen performance because each additional measure requires its own calculation and potentially increases the cardinality of the query result. More measures mean more data must be processed and rendered in the visual, making it slower and more memory-intensive. This is counterproductive when the goal is to reduce the initial data load and improve rendering time; the correct approach is to reduce complexity, not add more calculations.