Scenario practice questions

Load each file as a separate table and create relationships in the model.

Why it fails: This creates multiple tables, not a single combined table.

Use Power Query's 'Merge Queries' to join all files into one table.

Why it fails: Merge is for combining columns from different tables, not rows from multiple files.

Use 'Append Queries' to stack all files, then rename columns manually.

Why it fails: Append requires identical column names; renaming manually is error-prone.

Export the report to PDF and share the file.

Why it fails: PDF export yields a static, non-interactive snapshot with no data refresh, filtering or drill-through, so it cannot deliver a live report. It suits one-off distribution of a fixed view, not ongoing external sharing of an interactive report.

Share directly via Power BI using the user's email address.

Why it fails: Direct sharing requires the recipient to hold a Power BI Pro or Premium Per User licence to open the report, which the stem explicitly excludes. It is the standard route for internal colleagues already licensed, so it tempts when the external-user licensing constraint is overlooked.

Export the report to Excel and attach it to an email.

Why it fails: Exporting to Excel produces a static snapshot that strips interactivity, refresh and row-level security, and still requires recipients to hold a licence to open the underlying dataset. Excel export serves offline analysis by existing licensed users, not licence-free external report consumption.

Store data source credentials in the Power BI service.

Why it fails: Storing data source credentials in the Power BI service simply allows the service or an on-premises gateway to authenticate to the underlying database when refreshing data. This configuration does not impose any restrictions on what a viewer can see in the published reports; any user with access to the dataset can still see the full dataset unless a separate security layer like RLS is applied. Credential management is about connectivity, not about user-level authorization, so it does not protect sensitive diagnosis-specific data from unauthorized viewers.

Disable query caching for the dataset.

Why it fails: Disabling query caching for the dataset changes how Power BI caches query results to improve performance, but it has no bearing on data exposure or security. Query caching only affects the speed of dashboard tiles and report loads by reusing cached datasets; it does not alter row-level security, column visibility, or user permissions. Therefore, disabling it is a performance tuning decision that does nothing to prevent unauthorized users from seeing sensitive data.

Use encrypted connection to the database.

Why it fails: Using an encrypted connection to the database, such as TLS or SSL, secures data in transit between the database and the Power BI service or on-premises gateway. However, encryption protects against interception during transmission; it does not govern which rows or columns a report viewer is permitted to see after the data arrives in Power BI. Since the requirement is to prevent unauthorized users from viewing diagnosis-specific information, encryption alone is insufficient and does not address user-level access control.

Replace Values

Why it fails: Replace Values is a column-level transformation in Power Query that substitutes one specific value (e.g., text or number) with another, regardless of row context. It is essentially a find-and-replace operation and does not compare rows against each other, nor does it evaluate the uniqueness of records. Therefore, it cannot identify or remove duplicate rows; it only changes the contents of existing cells, leaving the row count and structure unchanged.

Filter Rows

Why it fails: Filter Rows is a conditional row-selection operation that keeps or removes rows based on logical predicates, such as numeric comparisons or text pattern matches. Unlike Remove Duplicates, it does not compare rows to one another; instead, each row is evaluated independently against the filter conditions. While it can exclude rows that happen to meet a criterion, it cannot guarantee that the remaining rows are unique, so it is ineffective for deduplication when the goal is to remove all duplicate records.

Group By

Why it fails: Group By is an aggregation transformation that groups rows by one or more columns and returns summary statistics (e.g., sum, count, average) for each group. This operation changes the data shape by collapsing multiple rows into a single row per group, but it does not retain the original granular rows; instead, it produces aggregated values. Although a creative custom aggregation might mimic row selection, Group By is not designed for deduplication and would lose the original record details, making it unsuitable for simply removing duplicate rows.

The measure does not filter out returns

Why it fails: Returns reduce revenue but not the distinct customer count, so including them inflates the numerator while the denominator stays fixed, raising CLV. It is tempting because excluding returns is a legitimate revenue-recognition refinement, but it would lower rather than raise the values, contradicting the observed symptom.

The measure should use COUNTROWS instead of DISTINCTCOUNT

Why it fails: COUNTROWS counts rows, not distinct customers, so the denominator would grow with transaction volume and CLV would fall, not rise. It is tempting because COUNTROWS is the correct choice when counting events or line items rather than unique entities, which is not what CLV requires.

The measure is dividing by zero for categories with no customers

Why it fails: Division by zero yields blank or infinity in DAX, not an inflated value, so it cannot explain higher-than-expected CLV. It is tempting because guarding against zero denominators with DIVIDE is a genuine best practise, but it addresses error handling rather than the observed overstatement.

A treemap.

Why it fails: Treemaps display hierarchical data as nested rectangles whose area encodes value, but comparing individual categories requires judging area, which human vision perceives less accurately than length. Because the pie chart's original categories are flat (not hierarchical), a treemap adds unnecessary visual structure without introducing a common baseline for easy comparison. Small differences between rectangle sizes are especially hard to resolve, so this option fails to improve accuracy.

A donut chart.

Why it fails: A donut chart is simply a pie chart with a center cut out, so it retains the same angular and arc-length comparison problem. In fact, the missing center removes the reference point that helps gauge angles, making relative magnitudes even harder to judge. Unlike bars, donut slices do not share a common baseline, so even with data labels the visual ranking is cognitively slower and more error-prone.

A scatter plot.

Why it fails: A scatter plot plots points in two continuous dimensions, so it is only appropriate when you need to show the relationship or correlation between two numeric variables. The 'users' pie chart is categorical, and a scatter plot would encode categories as point positions, which is meaningless without a second measure and would not convey part-to-whole or rank order. It also lacks a zero-aligned baseline for comparing magnitudes, making it a poor substitute for a bar chart.

Add the external users as members of the workspace and assign them a Viewer role

Why it fails: External users are not automatically present in your Microsoft Entra tenant, so they must first be invited as B2B guest users and accept the invitation. Without this prior step, you cannot add them to a Power BI workspace or assign roles like Viewer. Even if the external partner is already a guest, simply adding them to the workspace is not sufficient—you must also share the report or app for them to access the content. Thus, this option is incomplete because it omits the required guest invitation and content-sharing steps.

Publish the report to the web and share the public link

Why it fails: Publish to web generates a public URL that grants anonymous access to anyone with the link, completely bypassing Power BI security, row-level security, and licensing requirements. This is fundamentally unacceptable when the data is intended for a specific external partner, as it exposes the report to the entire internet. Additionally, the external partner would not need any identity or authentication, making the sharing uncontrolled and unaccountable. For security reasons, this method is never appropriate for sharing confidential or internal business data with a partner.

Embed the report in a secure portal using the 'embed for your organization' option

Why it fails: The 'embed for your organization' (secure embed) option is designed for content to be embedded in SharePoint or a portal where all users are already members of your tenant and have Power BI licenses. External partner users are not in your tenant by default, and this embedding method requires Microsoft Entra ID authentication, so without guest accounts or proper licenses, they will be blocked. Even if you invited them as guests, they would still need a Power BI license (unless the content is in Premium) and the embedding code would need to be configured for external users. Thus, this approach does not directly solve the sharing requirement for an external partner and is therefore incorrect.

The SQL Server credentials are not correctly configured in the data source settings.

Why it fails: Misconfigured SQL Server credentials would produce an authentication failure, such as 'Login failed for user' or an ODBC login timeout, not a privacy-level error. Privacy-level errors are unrelated to credential validation; they are raised during the query-folding and data-combination phase when Power Query checks whether sources can be merged under the current privacy settings. Therefore, this option points to a completely different class of data-source configuration issue.

The privacy levels for the SQL Server data source are set inconsistently across the environment.

Why it fails: Inconsistent privacy levels for the same SQL Server data source across different environments do not cause a privacy-level error within a single Power Query execution. Privacy level is a per-source, per-user property in Power BI Desktop, and the engine evaluates the combination of sources in the current session using those stored levels. The error requires at least two distinct sources being combined with incompatible settings, not a mismatch of settings for one source across machines or users.

The query uses a CSV file from a local folder that has a privacy level set to 'Private'.

Why it fails: The CSV file's 'Private' privacy level is a necessary contributing factor, but it is not sufficient on its own to trigger a privacy-level error. Power Query raises this error only at the point where data from the CSV is combined with another source, such as SQL Server, and the two sources' privacy levels are incompatible. If the CSV were used in isolation, no privacy fault would occur, so this option incorrectly identifies the CSV alone as the root cause.

IF

Why it fails: While `IF` can evaluate a single condition and return one of two results, it cannot directly produce three distinct categories ('Low', 'Medium', 'High') from a single expression without nesting multiple `IF` statements, which becomes unwieldy and error-prone. The requirement for a row-by-row categorisation into three tiers is better met by `SWITCH`, which evaluates an expression against a list of possible values cleanly. `IF` is tempting because it is the most intuitive function for simple binary logic, such as flagging sales as 'High' or 'Not High' based on a threshold.

CALCULATE

Why it fails: CALCULATE is not appropriate for row-level categorization because it is an aggregation function that modifies the filter context of a measure, typically to evaluate expressions under changed filters. It does not perform scalar per-row condition testing on a table row, and using it in a calculated column for categorizing values would be conceptually incorrect and likely produce errors or unintended results since it expects an aggregation as its first argument.

FORMAT

Why it fails: FORMAT is used to convert a value to text using a specified format string, such as formatting a number as currency or a date as a custom string. It does not perform logical comparisons or return category labels based on thresholds; applying FORMAT to a numeric measure would simply change its display representation, not assign it to a tier like 'Low', 'Medium', or 'High'.

Split the column into year, month, day and then combine.

Why it fails: Splitting the column into year, month, and day components is an unnecessarily convoluted approach; while it can work, it requires writing custom logic to detect the separator and pad values, and then recombining them with DATE or a similar function. This manual parsing is prone to error with varying formats (e.g., 'MM/dd/yyyy' vs 'dd-MM-yyyy') and locale differences, whereas changing the data type to Date in the Power Query Editor uses the built-in parser, which already handles these cases automatically. It also adds multiple transformation steps that complicate your query rather than solving the problem in one direct action.

Use the Excel Power Query add-in.

Why it fails: This option is invalid because Power Query is fully integrated into Power BI Desktop as its native data transformation environment; there is no separate 'Excel Power Query add-in' for Power BI, and even if you were thinking of the Excel add-in, it is irrelevant to a Power BI solution. In Power BI Desktop, you access Power Query via the 'Transform Data' button, which opens the Power Query Editor where you can directly change the column data type. Invoking an external add-in would be unnecessary, unsupported, and inefficient for a task that is a single built-in step.

Create a calculated column in DAX using DATEVALUE.

Why it fails: Calculated columns in DAX are executed in the analysis services engine after the data has been loaded into the model, not during the extraction/transformation phase; therefore, using DATEVALUE in a calculated column addresses the symptom after the fact, and it would still leave the column as text in the data model unless you also change the data type of the new column. Moreover, DATEVALUE is locale-sensitive and might fail on dates like '31/12/2023' in certain contexts. The correct action is to fix the column during transformation using Power Query's 'Change Type' feature, which ensures the data is correctly typed before it ever enters the model.

Increase the cache size in Power BI Desktop

Why it fails: Cache size in Power BI Desktop controls the memory allocated for storing imported data in Import mode, so adjusting it has no effect on DirectQuery connections. DirectQuery bypasses the local cache entirely and executes queries against the remote source on demand; therefore, increasing the cache size in Power BI Desktop will not improve report performance and may even be misleading as a troubleshooting action. Performance in DirectQuery is governed by source database query plans, network latency, and gateway throughput, none of which are addressable by client-side cache settings.

Switch the model to Import mode

Why it fails: Although Import mode typically offers faster query response times by maintaining an in-memory compressed snapshot, it is not a general-purpose substitute for DirectQuery because it sacrifices real-time data accuracy and requires scheduled refresh or full loads. In scenarios where the underlying data changes frequently or is subject to Row-Level Security (RLS) at the source, Import mode may be non-compliant or impractical. Switching models is a major architectural change that involves storage, refresh, and data-currency trade-offs; it is not a targeted fix for a DirectQuery-specific performance issue and may be unavailable due to size or governance constraints.

Reduce the number of distinct values in Region

Why it fails: Lowering the cardinality of the Region column by consolidating or grouping values is an anti-pattern that can degrade analytical usefulness and does not address the physical query execution cost—the database still has to scan or seek the underlying table based on the predicate. Even with fewer distinct values, the search complexity for a filter remains similar unless the column is indexed; the source database query optimizer may also produce the same access plan if the column selectivity changes only marginally. Moreover, reducing distinct values irreversibly loses business detail (e.g., merging regional territories) and undermines the purpose of having a slicer, making it an unacceptable trade-off for performance.

VALUES

Why it fails: VALUES returns a single-column table of distinct values from the specified column. When used inside CALCULATE as a filter, it would actively filter to those specific values rather than removing filters, so the denominator would be a filtered total, not the grand total. This makes VALUES unsuitable for calculating a percentage relative to the overall total, because it preserves the current context instead of clearing it.

ALLSELECTED

Why it fails: ALLSELECTED is specifically designed to respect the user's outer filter context, such as slicer selections, while removing filters from inner query dimensions. This means the denominator would reflect the total of the currently selected categories, not the grand total across all data. For a percentage of the full dataset, you need to ignore those slicers, so ALLSELECTED yields an incorrect result unless you actually want a percentage of the selected total.

REMOVEFILTERS

Why it fails: REMOVEFILTERS can technically clear filters and would work inside CALCULATE to produce a grand total, making it a possible alternative. However, ALL is the more standard, conventional, and widely documented function for percentage-of-total calculations in DAX, and it is the expected pattern in most training materials. While REMOVEFILTERS is explicit about its purpose, ALL is more concise and familiar to DAX developers, making it the correct choice for this classic scenario.

The relationship is set to filter in the wrong direction

Why it fails: The relationship direction is correct: the Date table filters the Sales table via a one-to-many relationship, which is the required orientation for DAX time intelligence functions. A wrong filter direction would typically cause measures to return values for the wrong granularity or not propagate filters from the date slicer to the fact table. Here, since the direction is from Date to Sales, filters flow properly, so this is not the cause of the blank result.

The measure uses incorrect syntax for DATESYTD

Why it fails: The DAX syntax for DATESYTD is exactly correct: DATESYTD('Date'[Date]) takes a single column reference to a date column, which is precisely what is used. Invalid syntax would be something like DATESYTD('Date') or DATESYTD(SUM('Date'[Date])) or missing the closing parenthesis. Since the function name, argument, and parentheses are all proper, syntax errors cannot explain the blank output.

The 'Date' table is not marked as a date table

Why it fails: The Date table is explicitly marked as a date table in the model, as stated in the exhibit, and DATESYTD will function only on a table that is marked as a date table. Being marked as a date table enables DAX to automatically generate the continuous date range that time intelligence functions rely on, but it does not automatically fill in any missing dates in the underlying column. Because the table is already marked, that requirement is satisfied, so the problem must lie elsewhere—specifically in actual date values present in the column.

Many-to-many (*:*)

Why it fails: A many-to-many relationship between Orders and Customers would require a junction or bridge table because each order could theoretically be associated with many customers and each customer with many orders. This directly contradicts the business rule that every order is placed by exactly one customer. Without a linking table, the model would produce duplicate order rows when filtering from Customers, causing fan-out and incorrect aggregations.

One-to-many (1:*) from Orders to Customers

Why it fails: A one-to-many (1:*) relationship from Orders to Customers would reverse the direction and imply that a single order can be linked to many customer records. That is semantically impossible because each order's CustomerID points to exactly one customer. In the correct model, the single side must be Customers (one customer) and the many side must be Orders (many orders per customer), making the from-Orders-to-Customers direction many-to-one, not one-to-many.

One-to-one (1:1)

Why it fails: A one-to-one (1:1) relationship would restrict both tables so that each order can have only one customer and each customer can have only one order. That would prevent customers from placing multiple orders and would require CustomerID to be unique in the Orders table as well as in Customers. This does not match the common business scenario where a customer can place many orders, so one-to-one is incorrect.

The user does not have permission to view the report page.

Why it fails: This is incorrect because report page visibility is controlled by workspace roles or app permissions, completely separate from RLS. If the user lacks permission to view the page, they would receive an access denied error or the page wouldn't render at all, not a blank report with headers and axes visible. Since the user can actually see the report page and its layout, the root cause lies in the data-level security rules.

The user is viewing a dashboard tile instead of the report.

Why it fails: This cannot explain the blank visuals because row-level security is enforced on dashboard tiles exactly as it is on report visuals. When a dashboard tile is pinned from a report, the tile runs a query against the same dataset with the viewer's effective identity, so RLS filters are applied and can return an empty set for that user. If the tile itself were the issue, the user would see the tile but with no data, which is the same symptom as in the report.

The dataset was refreshed using an RLS bypass account.

Why it fails: The refresh account is irrelevant to RLS behavior because row-level security is a query-time filter, not a data-loading filter. During refresh, Power BI imports the full dataset into memory; when an end user opens the report, the Power BI engine applies the role's FILTER() predicate to their query, regardless of which account performed the refresh. Even if the refresh used a service principal with full permissions, that only affects data loading—not the row-level scoping applied to individual viewers.

Use bookmarks to switch between fixed date ranges.

Why it fails: Bookmarks capture and restore a predefined view state, including filters and slicer values, but they are authored once and cannot adapt to arbitrary user-selected dates. Clicking a bookmark simply jumps to that fixed snapshot, so it does not provide a freeform or self-service way to specify a date range. Bookmarks are better suited for story-like navigation between known scenarios, not for open-ended date filtering.

Create a calculated column that flags rows within the last 30 days.

Why it fails: A calculated column that flags rows within the last 30 days is evaluated once when the data is loaded or refreshed, so it becomes stale after a day and cannot react to user interaction. Even if you use a custom date selection, the column stores a fixed value per row and would not change when a user applies a different range. This approach is also inefficient because it adds storage and refresh overhead rather than leveraging Power BI's built-in dynamic filtering.

Apply a filter on the date field in the Filters pane.

Why it fails: Applying a filter in the Filters pane is a static, author-defined restriction that applies immediately to the visual or page, but viewers do not have an obvious interactive control to modify it without editing the report. The Filters pane is meant for persistent filters rather than ad-hoc date browsing, and it does not offer the convenient preset ranges and auto-updating behavior of a relative date slicer. It also hides the available date choices from the user because there is no visible selector on the canvas.

Connect to each CSV file individually and use Append Queries.

Why it fails: Connecting to each CSV file individually and then using Append Queries is functionally possible, but it is a manual, repetitive process that requires you to add every file separately and maintain that list whenever new files arrive. It also ignores the folder connector's built-in ability to discover and process all files at once, making it inefficient and error-prone compared to using the Combine Files transform.

Use Merge Queries to join the files based on a common column.

Why it fails: Merge Queries is the wrong tool here because it performs a relational join (e.g., inner, left outer) based on a common key column, which would horizontally combine columns from different tables and potentially duplicate rows, not stack the records from each CSV vertically. The goal is to append row sets from multiple files with the same columns, which is what Combine Files / Append Queries does, not a merge operation.

Change the data source settings to treat the folder as a single data source.

Why it fails: There is no data source setting that treats a folder as a single data source in the way the option implies; the folder connector simply returns a list of files as a query, but you still need to apply a transformation like Combine Files to actually load the contents of those files into one table. Changing data source settings only affects credentials, privacy levels, or gateway configuration, not how the folder's files are aggregated.

Use ArcGIS Map visual instead of the built-in map.

Why it fails: The ArcGIS Map visual, while it provides a slightly different geocoding engine and more styling options, still performs name-based geocoding when coordinates are absent. It does not guarantee that more cities will be recognized, and in fact, it may require you to enable geocoding or use the same underlying location fields. Therefore, simply swapping the visual does not address the underlying issue that store locations lack reliable spatial coordinates—adding latitude and longitude remains the definitive fix.

Change the map visual to a filled map.

Why it fails: Switching to a filled map changes the visual from point-based markers to shaded region polygons, but it still relies on geocoding to match the city field against a spatial dataset of region boundaries. Without latitude and longitude, a filled map will still fail to display data for unmatched cities, and it can even obscure the problem by rendering a whole region incorrectly (e.g., placing data for 'Springfield' into the first state match), so it does not solve the accuracy issue.

Ensure the city column is set to the Text data type.

Why it fails: The city column being set to the Text data type is actually the expected state for Power BI geocoding—Power BI automatically recognizes text fields like City, State, and Country/Region. Changing the data type to Text does not help because the geocoder already accepts text; the failure occurs when a city name is missing, ambiguous (multiple cities with the same name), or not recognized by the geocoding service, not because the field is not text.

The 'Export to Excel' setting is disabled in the Power BI admin portal.

Why it fails: The 'Export to Excel' setting in the Power BI admin portal is a tenant-wide toggle under Tenant settings. If disabled, the export icon would be unavailable for every user and every report, but the scenario describes a selective block tied to the user or file, indicating the tenant setting is still enabled and points instead to a session policy.

A sensitivity label with high classification is applied to the report, automatically blocking export.

Why it fails: Sensitivity labels classify data and can apply encryption, but a label alone—even one marked 'Highly Confidential'—does not automatically remove the Export permission in Power BI. For a label to block export, it must be accompanied by an encryption template that excludes the user's access rights or by a Defender for Cloud Apps/DLP policy that acts on that label. Therefore, the label is not the direct cause.

The user is an external guest user.

Why it fails: External guest users can legitimately open and export Power BI reports if they have Viewer or higher permissions on the workspace, because Power BI applies the same export controls to guests as it does to internal users by default. Unless a Conditional Access policy or the external sharing settings are explicitly configured to block guest downloads, guest status alone does not trigger export failure. A Defender session policy, not the guest account type, is what enforces the block.

The map visual does not have a legend field, so clicking does not generate a filter.

Why it fails: A legend field is not required for a map visual to send cross-filters. When you click a bubble on a map, Power BI automatically sends a filter on the field(s) bound to the Location well, such as City or Region. The legend only determines visual grouping and color coding; it does not control the cross-filtering behavior, so the absence of a legend is irrelevant to the problem.

The table visual has a filter that prevents cross-filtering.

Why it fails: There is no mention of a filter applied to the table visual in the scenario, so this is a speculative explanation. Even if a visual-level filter existed, it would not prevent cross-filtering; it would only restrict the table's rows after the filter is applied, possibly to zero rows, which could mimic a lack of filtering. The actual cause is a mismatch in the field being used for the relationship, not an overt filter on the table.

The region slicer is interfering with the cross-filtering.

Why it fails: Slicers in Power BI act as independent filters on the report page; they do not block or suppress cross-filtering from other visuals. A slicer only constrains the data that is visible in the visuals it affects, but when you click a data point on a map, the cross-filter is still applied to other visuals using the underlying relationship. The propagation of a cross-filter is determined by the data model's relationships and the fields used in the target visual, not by whether a slicer is present.