Hard Difficulty Questions

The CALCULATE function is misused and should be replaced with SUMX

Why it fails: 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 it fails: 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 it fails: 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.

To avoid using an on-premises data gateway

Why it fails: 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 it fails: 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 it fails: 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 it fails: 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 it fails: Removing unnecessary columns reduces memory and processing time.

Split columns by delimiter to normalize data.

Why it fails: 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 it fails: 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 it fails: 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.

The Customer and Date tables are not related to each other.

Why it fails: The Customer and Date tables are both dimension tables connected to the Orders fact table, but they do not need a direct relationship to each other. In a star schema, filter context flows from one dimension to another through the fact table, so a Date slicer on Orders can propagate to Customer via the shared Orders rows. The absence of a direct Customer–Date relationship is therefore not a problem and cannot explain why the slicer is ignored.

The relationship between Orders and Date is inactive.

Why it fails: The scenario explicitly states that the relationship between Orders and Date is active, meaning the default filter propagation from Date to Orders is enabled. An inactive relationship would require the measure to use USERELATIONSHIP to activate it, which is not the case here. The real issue is not the relationship's state; rather, the measure itself may be removing the Date filter context using ALL or ALLEXCEPT, which overrides even an active relationship.

The relationships are set to single direction, so filters from Date do not propagate to Orders.

Why it fails: Single-direction relationships in Power BI allow filters to propagate from the 'one' side to the 'many' side, so a Date slicer can absolutely filter Orders in a single-direction relationship from Date to Orders. The directionality is correctly configured and does not hinder propagation of the Date year filter. The problem lies not in the relationship direction but inside the measure's filter modifiers, which can strip the Date filter context regardless of relationship direction.

Replace incremental refresh

Why it fails: Calculated tables are created during the model refresh process, but they do not support incremental refresh policies. Incremental refresh applies to Power Query-based imported tables using rangeStart and rangeEnd parameters to partition source queries; because a calculated table is fully materialized in memory from DAX and has no source-level query folding, it cannot be filtered by date windows. Thus, replacing incremental refresh is not a valid reason to create a calculated table.

Modify the source data before loading

Why it fails: A calculated table operates on in-memory model data after the data has already been loaded and refreshed, so it cannot modify or transform source data before ingestion. To clean, filter, rename, or change source values, you must use Power Query (M) steps in the get-data stage, which are performed before the data enters the model. Relying on a calculated table for source modification would create a circular dependency and violates the correct data-flow order.

Disable the 'Enable load' option for the SalesTable

Why it fails: Disabling Enable load only hides SalesTable from the data model; it does not alter query folding or move the filter upstream. It tempts when trimming model size, but the fix here is a step that folds, such as filtering before the Group By.

Use CALCULATE in DAX to filter

Why it fails: CALCULATE is a DAX function evaluated in the data model after Power Query has loaded the data, so it cannot push filtering to the source. It tempts for measure-level filtering, but the requirement is a Power Query step that folds upstream.

Add a 'Table.Buffer' step after the filter

Why it fails: Table.Buffer caches the table in memory at that point, preventing folding and forcing the filter to run locally after retrieval. It tempts for repeated downstream references, but it blocks the source-side filtering the scenario requires.

On the Add Column tab, select Format and then Trim.

Why it fails: Using Trim from the Add Column tab creates a new column containing the trimmed values while leaving the original column unchanged. The scenario explicitly requires the transformation to apply to the entire column without creating a new column. Although the new column would have correct values, the original StudentEmail column would remain untrimmed and the relationship would still fail.

On the Transform tab, select Replace Values and replace a space with an empty value.

Why it fails: Replace Values replaces every occurrence of the specified substring, including spaces inside email addresses or names, which would corrupt the data. It also does not selectively target leading and trailing spaces. This approach would damage valid values and is not a safe way to trim whitespace from a column used as a relationship key.

On the Transform tab, select Format and then Clean.

Why it fails: The Clean transformation removes non-printable characters, such as control codes, but it does not remove ordinary leading and trailing spaces. Spaces are printable characters, so the values would remain with their extra whitespace and the relationship would still not match. Clean is not the correct tool for stripping spaces from text keys.

Switch to a table visual.

Why it fails: 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 it fails: 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 it fails: 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.

The relationship is configured as many-to-many.

Why it fails: 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 it fails: 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 it fails: 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 it fails: 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 it fails: 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 it fails: 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.

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

Why it fails: 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 it fails: 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 it fails: 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.

The calculated table lacks a relationship to the Sales table.

Why it fails: Calculated tables are computed from source tables and can be linked to other tables with model relationships, but a relationship is not a prerequisite for their existence or use. Even if a relationship to the Sales table existed, the calculated table's rows are fixed when the model is refreshed, so slicers cannot recompute those rows. Therefore, the lack of a relationship is not the cause of the slicer unresponsiveness.

The SUMMARIZE function syntax is incorrect.

Why it fails: The SUMMARIZE function is syntactically valid for creating a calculated table; for example, SUMMARIZE(Sales, Sales[Product], "Total", SUM(Sales[Amount])) produces a valid static grouped table. The absence of an error and the presence of a table result indicate incorrect syntax is not the root cause. Furthermore, SUMMARIZE in a calculated table is evaluated once at refresh, so its correct syntax cannot make the table respond dynamically to slicers; the design choice, not the DAX grammar, is the problem.

The calculated table is not marked as a date table.

Why it fails: Marking a table as a date table is a metadata setting that enables time intelligence functions such as TOTALYTD and can affect relationship behavior, but it has no effect on when the table's data is computed. A date-marked table is still a static calculated table and will not react to slicer selections in any way. Thus, the date-table flag being unset is irrelevant to the symptom of unchanging values.

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 it fails: 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 it fails: 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 it fails: 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 a custom visual that combines both views in one chart

Why it fails: 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 it fails: 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 it fails: 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.

Use the Column From Examples feature, entering '1' next to 'Monthly' and letting Power BI infer the remaining mappings.

Why it fails: Column From Examples generates M code based on patterns it detects, which works well for consistent formats but is unreliable for arbitrary text labels. With only one example, the inferred logic may not correctly map 'Annual' to 12 and 'Quarterly' to 3. This approach risks producing incorrect values silently, which is worse than an explicit mapping.

Create a DAX calculated column in the semantic model that uses SWITCH to translate each billing period into months.

Why it fails: A DAX calculated column would work functionally, but the scenario explicitly requires the transformation to be added in Power Query. Calculated columns also consume memory in the model and cannot be referenced during other Power Query steps. The requirement is scoped to the data preparation layer, so DAX is the wrong tool here.

Change the data type of the BillingPeriod column to Whole Number and rely on Power Query's automatic value conversion.

Why it fails: Power Query cannot convert the text 'Monthly' into a number because it contains no numeric content. Changing the type would produce errors in every row rather than the intended month counts. Type conversion only works when the source text is already numeric or parseable as a number, which is not the case here.

Use the Group By transformation to group by AccountID, then apply the All Rows operation, and finally expand the rows to calculate the running total.

Why it fails: Group By with All Rows creates nested tables per group, but it does not automatically compute a running total. Expanding the rows would still require custom logic to accumulate Amount within each group. This method does not provide a built-in running total and would not satisfy the no-code requirement.

Sort the table by AccountID and TransactionDate, then use the Add Column tab and select Index Column, and then use the Table.AddIndexColumn function with a custom formula.

Why it fails: Using Table.AddIndexColumn with a custom formula involves writing M code, which the scenario explicitly forbids. The built-in Index Column transformation alone does not produce a running total. This option combines a built-in step with custom code, so it does not meet the requirement.

Sort the table by AccountID and TransactionDate, then use the Add Index Column transformation, and finally use a custom column to calculate the running total.

Why it fails: The Add Index Column transformation adds a sequential row number, but it does not calculate a running total. A custom column would require writing M code, which the scenario prohibits. This approach does not meet the requirement of a no-code running total that resets per account.

Add Country, City, and Store to the Rows well and enable 'Stepped layout'.

Why it fails: Stepped layout indents each level, which can help readability, but it does not allow users to see all levels expanded simultaneously. It still requires drill-down or expand actions. The requirement is to show all levels at once, which stepped layout alone does not achieve.

Add Country, City, and Store to the Rows well and enable 'Expand all' by default in the field well.

Why it fails: There is no 'Expand all' option directly in the field well for matrix visuals. While you can expand all levels manually in the visual, it is not a configuration setting that persists. This option misrepresents the available functionality.

Add Country, City, and Store to the Rows well and enable 'Show all levels' in the formatting options.

Why it fails: 'Show all levels' is not a valid formatting option in Power BI matrix visuals. There is no such setting. Users can expand all levels manually or use drill-down, but there is no single toggle to show all levels by default. This option is incorrect.

CALCULATE(SUM(Transactions[Amount]), FILTER(ALL(Transactions), Transactions[TransactionDate] <= MAX(Transactions[TransactionDate]) && Transactions[AccountID] = MAX(Transactions[AccountID])))

Why it fails: This pattern calculates a running total but does not reset at the beginning of each year. It also uses ALL(Transactions) which removes all filters, and the condition on AccountID may not correctly group by account when multiple accounts are in context. It lacks a year-based reset and proper handling of account context.

CALCULATE(SUM(Transactions[Amount]), DATESYTD(Transactions[TransactionDate]), VALUES(Transactions[AccountID]))

Why it fails: DATESYTD calculates year-to-date totals but requires a marked date table and does not automatically reset per account. It also assumes a calendar year and does not handle the account grouping as specified. Without a proper date table and additional context, this measure would not produce the desired running total per account.

CALCULATE(SUM(Transactions[Amount]), FILTER(ALLSELECTED(Transactions[TransactionDate]), Transactions[TransactionDate] <= MAX(Transactions[TransactionDate])), VALUES(Transactions[AccountID]))

Why it fails: This pattern attempts to use ALLSELECTED on the date column and VALUES on AccountID, but it does not reset annually. The FILTER over ALLSELECTED(Transactions[TransactionDate]) will include all dates up to the max date in the current context, regardless of year. Additionally, using VALUES as a filter argument may not correctly enforce the account grouping.

Change the relationship between Subscriptions and Dates to bidirectional cross-filtering and count the Subscriptions rows.

Why it fails: Bidirectional cross-filtering changes filter propagation but provides no mechanism to test whether a subscription's StartDate and EndDate bracket the selected date. It also introduces ambiguity when multiple date columns relate to the same Dates table, and it cannot produce an interval-overlap count on its own.

Create a measure that uses CALCULATE with USERELATIONSHIP to activate the relationship between Subscriptions and Dates inside the measure.

Why it fails: USERELATIONSHIP activates an existing inactive relationship for the duration of the CALCULATE, but it can only engage one relationship at a time. Counting active subscriptions requires testing both StartDate and EndDate against the selected date, so a single USERELATIONSHIP cannot express the overlapping interval and would undercount or overcount subscriptions.

Create a calculated column on Subscriptions that flags active rows, then use COUNTROWS on the filtered Subscriptions table in the measure.

Why it fails: A calculated column is computed at refresh time and cannot respond to a slicer selection made at query time, so it cannot evaluate 'active on the selected day.' It would also require storing a flag per row, and the flag would be stale as soon as the slicer changes, making the measure return incorrect results.