Visualize and analyze the data practice questions

DATEADD

Why wrong: DATEADD('Date'[Date], -1, YEAR) shifts each selected date by exactly 365 or 366 days, preserving individual date values rather than calendar month boundaries. While this can work for daily or continuous data, a month-level visual may show mismatched day-to-day alignment, such as comparing February/2024 with February/2023, and any missing dates in the Date table become excluded from the result. Since the report is measuring the same period last year, DATEADD is less likely to be the root cause when the expected behavior is calendar-month alignment.

SAMEPERIODLASTYEAR

Why wrong: SAMEPERIODLASTYEAR is the most likely cause because it returns the same period from the previous year, but if the Date table lacks data for the entire previous period, it can produce incorrect results for month-over-month comparisons.

PREVIOUSYEAR

Why wrong: PREVIOUSYEAR('Date'[Date]) entirely ignores the active month or quarter filter and returns all dates from January 1 to December 31 of the prior year. In a month-level visual, this means each row would receive the same full-year previous-year value, so the year-over-year calculation would compare a single month to a sum of twelve months. That behavior is clearly incompatible with the required same-period comparison, making it an unlikely cause when the symptom is inconsistent month-over-month differences.

Create a date hierarchy with Year and Month.

Why wrong: Creating a date hierarchy with Year and Month is unnecessary here because the slicer and line chart can use separate Year and Month columns directly. Hierarchies are only a convenience for drill-down, not a solution for data modeling or time-intelligence issues. Adding a hierarchy does not affect how Power BI interprets dates or sorts months, so it leaves the underlying problem unresolved.

Set the slicer to 'Single select' mode.

Why wrong: The slicer's single-select mode is a user-facing interaction option only; it does not alter the data model or how the underlying date table is processed. Whether the slicer allows one or multiple selections, the year filtering still works correctly. Since the issue is likely related to the date table being unrecognized, changing the slicer mode has no impact on the correctness of the visual.

Hide the Month column in the Date table.

Why wrong: Hiding the Month column in the Date table would make it unavailable in the field list, preventing the line chart from using it as an axis field. This action does not fix any date-table configuration issue; it removes a field that is essential to the visual you are trying to display. Hiding only affects field visibility, not filter behavior or time intelligence, so it would break the report rather than repair it.

Use COUNTROWS without any filter.

Why wrong: COUNTROWS with no arguments (or COUNTROWS(Orders)) evaluates the entire Orders table with no row restriction, so it returns the total number of orders, including those shipped after more than 3 days. This ignores the core business requirement of isolating the fast-shipping subset. Because the measure must respect the date-difference condition, an unfiltered COUNTROWS is fundamentally incorrect and will always overcount.

Use SUM to add up the Amount column.

Why wrong: SUM(Orders[Amount]) adds up the numeric values in the Amount column, producing a total dollar amount rather than a count of orders. Even if you wrapped this SUM in a FILTER or CALCULATE, you would still be aggregating money, not counting rows. The question asks for 'the number of orders' (a cardinality), so a counting function like COUNTROWS or COUNT is required; SUM is semantically mismatched.

Increase the date range filter to include more data

Why wrong: Increasing the date range filter to include more data directly expands the number of rows that DirectQuery must retrieve and process from the relational source. DirectQuery sends each aggregation to the source database without caching results, so a wider date range means longer query execution, larger data transfers, and heavier load on both the database and the report. This is opposite to performance improvement—it will slow down the report, especially when combined with DirectQuery's lack of in-memory compression.

Disable cross-filtering in the report

Why wrong: Disabling cross-filtering in the report only changes how visuals interact on the canvas, but it does not change the underlying DirectQuery queries that are sent to the source database. Cross-filtering affects slicer interactions and visual highlighting, but each visual already generates its own DAX queries; turning off cross-filtering might reduce the number of queries in some interactions, but it doesn't address the root performance problem of querying large fact tables directly. It could even confuse report users, as they would lose the ability to filter related visuals, while the actual date-filtering performance remains unchanged.

Remove unnecessary columns from the date table

Why wrong: Removing unnecessary columns from the date table can slightly reduce the data model size, but a date dimension is already a relatively small table compared to the fact table, and in DirectQuery the date table may be sourced in a way that still requires querying the database. The primary performance bottleneck for date filtering is not the width of the date table but how the relationship and filters are pushed down to the fact table; trimming columns has negligible impact on the date-filtering query path. In fact, if the date table is also in DirectQuery, it might not even be cached, so column removal wouldn't help much either.

Measures are using CALCULATE with multiple filter conditions

Why wrong: Measures are not involved in the refresh pipeline. A scheduled or manual refresh only performs Power Query extract/transform/load steps and writes data into the model; measures are computed on demand when visuals or queries execute DAX against that model. CALCULATE with multiple filter conditions changes filter context during a query and can slow report interaction, but it has zero effect on refresh duration.

The DirectQuery table is set to Import storage mode

Why wrong: DirectQuery storage mode is intrinsic to the table when its data source connection is DirectQuery; you cannot simply change it to Import storage mode without altering the underlying connection/partition configuration. In a composite model, a table from a DirectQuery source remains DirectQuery by design, so the stated option is impossible and cannot be the cause. Moreover, if it somehow were Import, the model would no longer have a live DirectQuery table causing refresh delays.

The report contains many complex visuals that need to be rendered

Why wrong: Visual rendering and DAX query execution happen at user interaction time, not during the refresh process. A refresh operation loads and transforms data into the model and never draws or renders report pages, regardless of how many complex custom visuals exist. Complex visuals can slow report opening and response times, but they have no bearing on how long a scheduled refresh takes.

Use Power BI App permissions to restrict data

Why wrong: App permissions in the Power BI service control who can open the app or its reports, but they are an access-management layer, not a data-filtering layer. Every user granted access to the app sees the identical underlying dataset rows unless additional RLS roles are defined and applied on the dataset itself. Therefore, app permissions alone cannot hide specific rows or columns for individual users.

Create a static role for each manager and assign users

Why wrong: Creating a separate static RLS role for each manager hard-codes the organizational structure into the model, so any reorg or new hire requires editing roles and reassigning users. This approach becomes unmanageable as the company grows and violates the principle of single-source-of-truth for hierarchy data. A dynamic role using DAX is preferable because it derives the reporting chain from the data itself, eliminating manual maintenance.

Apply RLS at the visual level using bookmarks

Why wrong: Bookmarks and visual-level filters only alter the view state of a report—they can hide visuals or apply filters that any user can change or reset. Row-level security is enforced by the dataset engine before data is sent to a visual, so a bookmark cannot restrict the underlying rows returned by the query. Relying on bookmarks would leave the data exposed to users with direct query or build permissions on the dataset.

Increase the default page-level filter to limit data

Why wrong: A page-level filter limits the rows scanned per query, but it does not reduce the number of queries issued—every visual still runs its own query with the filter applied. The overhead of parsing, planning, and executing dozens of queries remains, so page-load time is not substantially improved. Moreover, if the filter uses a high-cardinality column or complex predicate, the performance gain from scanning less data may be negligible, making this a weak solution for a problem rooted in query volume rather than data volume.

Use a slicer to let users choose what to see

Why wrong: A slicer is an interactive filter that applies its selection only after the page has already rendered; initial page load still runs full unfiltered queries for every visual, so load time is unchanged. The slicer itself is another visual that adds its own query and rendering cost, and adjusting it afterwards triggers re-queries on all dependent visuals. Therefore, slicers improve usability and post-load filtering but cannot reduce the initial query count or the burden of a page crowded with visuals.

Create aggregations on the dataset

Why wrong: Creating aggregations pre-summarizes data to speed up individual query execution, but each visual still issues its own query, so the total number of query requests remains the same. Even if all queries hit a smaller aggregate table, the engine must still plan and execute each one, and the overhead of many round-trips persists. Aggregations also demand extra storage and careful design—they only help when the visual groupings align with the aggregation granularity—so they are a complementary optimization, not a substitute for cutting visual count.

A subscription

Why wrong: A paginated report subscription is a delivery mechanism that sends the report output via email or to a file share on a schedule. It is a feature of the Power BI service or Report Server, not a constituent part of the report definition itself; a report can exist and be rendered without any subscription. Subscriptions depend on the report being published and do not influence the report's data retrieval or layout. Therefore, subscriptions are not required components.

A map visual

Why wrong: A paginated report can contain any number and type of data regions, such as tables, matrices, charts, or maps, but none of these is mandatory. The map visual is only one of many available visualization types used to display spatial data geographically. A paginated report must have a data region to present data, but the choice of that data region is driven by the report design, not by a requirement. Consequently, a map is not a required component.

A query parameter

Why wrong: Query parameters allow user input to filter the data returned by the dataset query, but their inclusion is entirely dependent on the report's requirements. A paginated report can function perfectly well with a static dataset query that returns a fixed set of data, or it can use report parameters that do not even interact with the query. Because parameters are only needed to make reports dynamic or interactive, they are not a mandatory part of the report infrastructure. Hence, a query parameter is not a required component.

The table name Sales does not exist in the database

Why wrong: The Sales table not existing would cause an immediate refresh failure at the source connection step, typically raising a DataSource.Error or 'Invalid object name' message. The existing note confirms the code runs successfully initially, proving the table is present. A missing table cannot produce the kind of key-match error described, because the failure would occur before any table operations.

The ProductID column has duplicate values

Why wrong: Duplicate ProductID values do not break Table.Group operations, because grouping aggregates all rows sharing the same key into one group without requiring uniqueness. A key-not-found error would more plausibly come from operations like Table.Lookup or a merge that expects a single matching row, not from grouping. Therefore, duplicates are irrelevant to the reported failure.

The data type of the Year column is text instead of number

Why wrong: If the Year column were stored as text, comparing it to the numeric literal 2021 would result in a type mismatch that M handles by returning false for all comparisons, producing an empty table rather than a key error. The slicer would simply show no rows, not raise a database-level key lookup failure. Thus, a text-typed Year column cannot explain the observed M code error.

No one can read any datasets

Why wrong: This option is impossible because the Azure Policy definition contains an explicit conditional branch that permits dataset reads when the workspace path resolves to the finance workspace. The deny effect is scoped to non-matching workspace paths, not to the entire data plane, so read operations in finance continue to succeed. Thus, the statement that no one can read any datasets overstates the policy's reach and ignores the allowed condition.

Users cannot read datasets in the finance workspace

Why wrong: This option reverses the policy's condition logic. The policy's `if` expression checks whether the workspace is the finance workspace and, if true, the read action is allowed; the deny effect fires only when the workspace is something other than finance. Since finance is exactly the workspace that is explicitly exempted from the deny, users with appropriate roles there can read datasets, making this statement false.

All users can read datasets in all workspaces

Why wrong: This option fails on both the workspace and user dimensions. The policy denies read for any workspace whose path does not match finance, so all-workspace access is blocked. Additionally, Azure Policy does not grant read permissions to all users; it merely denies non-compliant actions, and the only users who can read are those holding a role in the finance workspace. Therefore, neither 'all users' nor 'all workspaces' reflects the policy's effective behavior.

Sales PY = CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD(Date[Date], -1, YEAR))

Why wrong: PARALLELPERIOD shifts the entire date range backwards by one year, but it returns a full-year period, not a range that mirrors the current filter context. If the current selection is a partial period such as quarter-to-date or year-to-date, it will retrieve the complete previous year, including dates outside the comparable span. This breaks the period-to-period equality needed for accurate year-over-year growth, making it more suitable for whole-period aggregates than for matching-month comparisons.

Sales PY = CALCULATE(SUM(Sales[Amount]), PREVIOUSYEAR(Date[Date]))

Why wrong: PREVIOUSYEAR returns the entire prior calendar year (or fiscal year, as defined by the date table's year-end date) irrespective of the current filter context. In a report showing a specific month or quarter, this formula will return the full previous year's total, not the matching period from last year. Because it changes the granularity of the comparison, it is only appropriate when the desired comparison is against the whole prior year, not for line-level year-over-year growth.

From the Format pane, add a measure.

Why wrong: The Format pane is exclusively for changing the visual appearance of an existing visual—such as colors, labels, borders, and conditional formatting rules—and it has no connection to model calculation objects. Measures are schema-level DAX formulas that live in the tabular model, not visual-level properties, so no command or section in the Format pane can add, edit, or define a measure.

From Power Query Editor, add a custom column.

Why wrong: Power Query Editor is the data transformation surface for M code; creating a custom column there appends a new column to the query result, not a measure to the model. A custom column is evaluated row-by-row at data refresh, whereas a measure is evaluated at query time with adjustable filter context—so adding a custom column is an ETL action, not a modeling action, and produces a static value rather than a dynamic DAX measure.

From the Visualizations pane, click New Measure.

Why wrong: The Visualizations pane (or the Build pane) is designed to configure visuals by dragging fields onto wells and selecting chart types; it contains no 'New Measure' command or DAX input box. If users mistake it for a creation surface, they are actually adding an implicit measure by dragging a numeric column into a visual, which is different from a user-defined explicit measure and does not provide complex DAX expression control.

Include all pages from the desktop report in the mobile layout.

Why wrong: Mobile layouts should be curated for the small-screen context; replicating every desktop page forces mobile users to pan and scroll through dense dashboards that were designed for a wide canvas. Reporting best practice is to reduce the mobile experience to the most critical KPIs and insights, so including all desktop pages directly contradicts the goal of streamlined, at-a-glance mobile consumption. This also increases load time and requires users to hunt for relevant information, degrading the mobile experience.

Use only responsive visuals that automatically resize.

Why wrong: Not all Power BI visuals are fully responsive; many built-in and custom visuals require manual adjustment to render well on a mobile layout, and even 'responsive' visuals cannot handle all types of data or axis configurations gracefully. Relying solely on automatic resizing can produce truncated labels, overlapping elements, or unreadable charts, especially with dense text or many series. The supported approach is to combine the mobile layout designer with visual-level settings (like font size and padding) rather than trusting universal auto-resize.

RunningTotal = CALCULATE([TotalSales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH), VALUES(Sales[ProductID]))

Why wrong: Unlike the correct version, VALUES(Sales[ProductID]) merely preserves the current filter context on ProductID without imposing any condition on store count. It does not iterate over all products to exclude those with fewer than 10 stores in the current month; it simply carries forward whatever product selection already exists. Additionally, without a CALCULATE-wrapped DISTINCTCOUNT per product, the measure cannot determine store eligibility, so products with low store counts remain included.

RunningTotal = CALCULATE([TotalSales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH), ALL('Calendar'))

Why wrong: ALL('Calendar') removes all filters from the Calendar table, which invalidates the date context needed for the running total. The MAX('Calendar'[Date]) inside DATESINPERIOD would then reference the overall maximum date in the calendar, not the selected month, so the rolling window shifts incorrectly. Moreover, ALL('Calendar') never applies the required store-count filter, so it fails both the date and product eligibility conditions.

RunningTotal = CALCULATE([TotalSales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH), FILTER(ALL(Sales[ProductID]), COUNTROWS(SUMMARIZE(Sales, Sales[StoreID])) >= 10))

Why wrong: COUNTROWS(SUMMARIZE(Sales, Sales[StoreID])) computes the distinct store count for each product across the entire Sales table (or the full filter context), ignoring the current-month constraint. This version evaluates the store count over all historical dates, so products that used to have 10 stores but not this month would be incorrectly included. It also lacks DATESMTD or any month-limiting logic, making it ineffective for the 'at least 10 stores in the current month' requirement.

There are missing relationships between Shipments and the date table.

Why wrong: A missing relationship between the Shipments table and the date table would prevent date slicers and filters from propagating to the Shipments data. However, the line chart uses the ShipDate column directly from the Shipments table, so it would still display a distinct data point per unique ShipDate value. The lack of a relationship would not force all revenue values to collapse into a single constant line.

The ShipDate column is of type 'Text' instead of 'Date'.

Why wrong: When the ShipDate column is stored as Text instead of Date, Power BI cannot treat it as a continuous time series or apply date hierarchy logic. The line chart interprets each unique text string as a separate category, and if the values are not recognized as chronological, the chart may plot a single aggregate or a flat, constant line rather than varying over time. This directly matches the symptom described, making it the correct diagnosis.

The Revenue column is a measure that is not summing correctly.

Why wrong: A Revenue column that is a measure and summing incorrectly would still respond to the filter context of the chart. If the axis contains ShipDate, the measure would be recalculated for each ShipDate value, producing a varying line unless the measure is intentionally constant or the date context is removed. An incorrect sum would show wrong values, not a single horizontal line, so this does not explain the observed behavior.

Use the Quick Measure feature to create a 'Year-over-year change' measure.

Why wrong: The Quick Measure feature can generate a Year-over-year change measure, but it is a convenience tool, not a required step. It does not create or mark a separate date table, which is the foundation for time intelligence. Using it may produce code that still fails if the underlying model lacks a proper date table or relationships.

Apply the CALCULATE function to switch between years.

Why wrong: CALCULATE is a powerful function for modifying filter context, but it is generic and not inherently designed for year-over-year comparison. To compute a previous year's value, you would need to combine CALCULATE with a filter expression or a dedicated time intelligence function. Simply applying CALCULATE does not automatically shift time periods and is not a substitute for a proper date table.

Ensure the 'Sales' table has a relationship to the 'Date' table with a many-to-many cardinality.

Why wrong: A many-to-many relationship between the Sales table and the Date table is incorrect modeling for time intelligence. The proper relationship is many-to-one, where the Date table is on the one side and the fact table on the many side. Many-to-many relationships create ambiguous filtering and can cause aggregations to multiply incorrectly, breaking time intelligence calculations.

The parameters in Sql.Database are reversed; the correct order is database name first, then server.

Why wrong: The M function Sql.Database has a strict signature: the first argument is the server name and the second is the database name. The script's call `Sql.Database(ServerName, DatabaseName)` therefore correctly follows this order. Reversing them would make M attempt to connect to a server named as the database string, causing a connection failure. Thus, the claim that the parameters are reversed is false.

The Table.FirstN function is missing the second argument; it should be Table.FirstN(#"Sorted Rows", 10).

Why wrong: Table.FirstN requires a second argument that specifies the number of rows to return or a condition; the script already includes that argument as `10` in `Table.FirstN(#"Sorted Rows", 10)`. Omitting the second argument would generate a function error, but that is not the case here. The statement mischaracterizes a complete and valid function call.

The #date function cannot be used with a datetime column; use DateTime.From instead.

Why wrong: The #date function is perfectly valid for constructing a date value; when compared to a datetime column, Power Query implicitly coerces the date to a datetime at midnight (00:00:00) for the comparison. DateTime.From would convert a value into a datetime, but it is unnecessary here and would not alter the comparison logic. Therefore, replacing #date with DateTime.From is neither required nor beneficial.

Use the ALL function in the measure to ignore filter context.

Why wrong: Using the ALL function inside the measure would strip away the existing row and column filter context from the matrix, so every cell would show the same grand total rather than the correct date-sliced value. This does not repair the missing relationship; it merely suppresses context and yields misleading summaries. The root problem is that filters from the date table never reach the Sales table, and ALL does nothing to establish or activate a relationship.

Create an active relationship between the date table and the Sales table on the SaleDate column.

Why wrong: Creating an active relationship between the date table and Sales on the SaleDate column is the correct fix because it allows filter propagation: when the matrix displays date fields (e.g., year or month) from the date table, DAX will automatically traverse the relationship to filter matching rows in Sales. An active one-to-many relationship is the standard star-schema design for a date dimension and ensures all standard visual interactions and time-intelligence functions work as intended without extra DAX workarounds.

Modify the measure to use CALCULATE with USERELATIONSHIP to force the relationship.

Why wrong: CALCULATE with USERELATIONSHIP is only useful when a relationship already exists but is marked as inactive, so it can be temporarily activated within that measure for a specific calculation. In this scenario, if no relationship is defined at all between the date table and Sales, USERELATIONSHIP will fail or have no effect, and even if it did exist but was inactive, rewriting every measure with this pattern would be far less maintainable than simply setting the relationship to active in the model.

Click Get Data and select SQL Server, then open Power BI Desktop, then enter server name and database name and authentication, then select tables or write a SQL query and click Load or Transform.

Why wrong: In Power BI Desktop's import workflow, you cannot invoke the Get Data dialog before the application is running. The very first action must be launching Power BI Desktop, after which the Get Data experience becomes available. Placing Get Data first makes the sequence invalid, even though the remaining steps (SQL Server connector, server/database/authentication, and table/query selection) are in the correct relative order.

Open Power BI Desktop, then enter server name and database name and authentication, then click Get Data and select SQL Server, then select tables or write a SQL query and click Load or Transform.

Why wrong: The Get Data step is not optional before connection details; it is the gateway that initializes the SQL Server connector and its corresponding connection dialog. Typing server, database, and authentication information before selecting the data source is impossible because no connector exists yet to accept those values. In the correct workflow, Get Data must come immediately after opening Power BI Desktop and before any connection-specific fields are entered.

Open Power BI Desktop, then click Get Data and select SQL Server, then select tables or write a SQL query and click Load or Transform, then enter server name and database name and authentication.

Why wrong: Power BI can only display database tables or execute a SQL query after a connection has been established with the server and database, including successful authentication. Selecting tables or writing a query before providing those connection details is not possible because the Navigator experience and the Load/Transform options are dependent on an active data source connection. Therefore, the connection details must be entered before any data selection or transformation choice.

Import: Data is not stored locally, queries are sent to source.

Why wrong: This description is incorrect because it swaps the roles of Import and DirectQuery: Import does store a local copy of the data inside the Power BI dataset. The phrase 'data is not stored locally, queries are sent to source' precisely characterizes DirectQuery, where each visual issues a query against the original database. Using this description for Import would confuse the mode's storage model and lead to incorrect expectations about refresh and performance.

DirectQuery: Data is stored in Power BI cache.

Why wrong: This is incorrect because DirectQuery never caches the underlying table data in Power BI; each interaction sends a query back to the source and only the minimal result set for a specific visual may be temporarily held for rendering. Data caching in Power BI is a hallmark of Import mode, where the VertiPaq engine compresses and stores all table data. If DirectQuery cached the data, it would no longer provide real-time freshness and would undermine its purpose for large or live sources.