Refer to the Exhibit Practice Questions

The query will fail because the SQL query uses '>' with a string.

Why wrong: The date is a string literal, which is valid in SQL.

The SQL query should use a parameter for the date instead of a hardcoded value.

Why wrong: Hardcoding is not a performance issue, though it's less flexible.

The grouping should be done in SQL to reduce data volume.

Why wrong: Grouping in SQL would also reduce data, but the current approach is valid; the main issue is the filter.

Rows where Region is 'North' and 'South'.

Why wrong: The role filter only shows 'North', not both.

No rows, because there is a conflict.

Why wrong: There is no conflict; the role filter takes precedence.

Rows where Region is 'South' only.

Why wrong: The role filter overrides the table-level filter.

Change credential type to 'Service Principal' and provide the app ID and secret.

Why wrong: A Service Principal credential authenticates to Azure Blob Storage via Microsoft Entra ID, but the error states the container is private and requires authentication, not that the current identity lacks permissions. The anonymous credential is already failing because the container denies unauthenticated access, so switching to a Service Principal would provide authentication, but the question’s scenario does not specify that a Service Principal has been granted access to the container. It is tempting because Service Principals are commonly used for automated, non-interactive access to Azure resources, and would be correct if the container’s access control list (ACL) or Azure RBAC had already assigned the Service Principal the necessary Storage Blob Data Reader role.

Change credential type to 'Basic' and provide the storage account name and key.

Why wrong: Basic authentication sends a simple username/password payload that Azure Blob Storage does not accept; the Basic credential type is designed for SQL Server, MySQL, or other data sources that support user/password login challenges. Even if you supply the storage account name and the account key in the username and password fields, Power BI will not translate that into the Shared Key or SAS authorization scheme Azure Blob requires. For Azure Blob, Power BI expects Account Key, SAS, or Entra ID-based credential types, so selecting Basic will fail.

Change credential type to 'Windows' for the Azure Blob datasource.

Why wrong: Windows authentication relies on NTLM or Kerberos against a domain-joined environment, which is meaningful only for on-premises sources like SQL Server or file shares. Azure Blob Storage is a cloud service with no concept of Windows domain credentials, so Power BI will not even offer or accept Windows as a valid credential type for that data source. Even if it did, the storage service cannot verify a Windows user, making this option completely incompatible with the error at hand.

There is no relationship between Date and Product tables.

Why wrong: This option is incorrect because the absence of a relationship between Date and Product would not produce this specific error. SUMMARIZECOLUMNS can operate on any tables, and missing relationships would result in a cartesian product (if intentional) or a different evaluation behavior, but not a scalar measure failure. Relationship issues typically manifest as incorrect results or a 'related table not found' error, not this type of scalar mismatch. Therefore, this is not the most likely cause.

The SUMMARIZECOLUMNS function does not support multiple group-by columns.

Why wrong: This is not the cause because SUMMARIZECOLUMNS explicitly accepts multiple group-by columns; it is designed to return a table with one row per combination of the given columns. In fact, it accepts up to 76 columns in a single call, making multi-column grouping a core feature. The error occurs after the grouping is defined, during measure evaluation, so it is not about the number of group-by columns. The measure's inability to produce a scalar is the actual failure point.

The ORDER BY clause is not allowed in EVALUATE statements.

Why wrong: ORDER BY is a valid and commonly used clause in EVALUATE statements in DAX queries. The grammar for EVALUATE allows an optional ORDER BY clause that sorts the results based on expressions or columns, so it is fully supported in DAX. The error would occur before ordering is processed, because the measure is evaluated for each row in the result set. Thus, ORDER BY cannot be the cause of this error.

The RLS roles are missing the 'SalesRole' mapping to the dataset.

Why wrong: The roles are connected to the dataset; the issue is the effective identity.

The dataset does not have any RLS roles defined.

Why wrong: The exhibit shows roles are defined.

The user is not assigned to any RLS role.

Why wrong: The user is not listed, but the effective identity overrides RLS.

The measure does not filter out returns

Why wrong: Not relevant to the high values.

The measure should use COUNTROWS instead of DISTINCTCOUNT

Why wrong: COUNTROWS would count each transaction, not distinct customers.

The measure is dividing by zero for categories with no customers

Why wrong: DIVIDE returns 0 for division by zero.

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

Why wrong: 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 wrong: 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 wrong: 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.

Increase the number of partitions to monthly

Why wrong: Simply increasing the number of partitions to monthly does not automatically improve refresh performance. In Power BI, partitions are primarily managed by the incremental refresh feature; manually creating monthly partitions through the XMLA endpoint adds complexity and metadata overhead, yet without an incremental refresh policy, a full refresh will still reprocess all data each time, canceling any potential benefit. Moreover, excessive partitioning can degrade query performance due to increased metadata management and maintenance tasks rather than optimizing it.

Remove all partitions and load data as a single table

Why wrong: Removing all partitions and loading the data as a single table forces Power BI to perform a full refresh of the entire dataset on every scheduled refresh. This dramatically increases refresh duration and data load because there is no mechanism to isolate recent changes or historical data, meaning every row is re-read and reprocessed. Even if the table is moderately large, the absence of partition pruning raises memory pressure and query latency, making this option counterproductive for any scenario requiring efficient refreshes.

Change the storage mode to DirectQuery

Why wrong: Changing the storage mode to DirectQuery does not improve refresh performance; it eliminates the need to refresh imported data but introduces major trade-offs. DirectQuery issues live queries to the underlying source, making query performance dependent on source-system indexing and network latency, and it loses the in-memory compression and highly optimized query engine that import mode provides. This option changes the data-access strategy entirely rather than addressing partition configuration, and it requires a stable source connection, making it unsuitable as a solution for optimizing scheduled data refreshes.

The query returns total sales for each year, ignoring category

Why wrong: This is incorrect because SUMMARIZECOLUMNS explicitly lists both Year and Category as grouping columns, so the query returns one row per unique Year-Category combination, not a yearly total that collapses categories. To ignore category, Category would need to be removed from the GROUP BY list, causing the grouping to be solely by Year. Moreover, the Amount > 100 filter is applied to sales rows, but it does not drop Category from the result; it only limits which rows feed the aggregation.

The query returns sales amounts only for products with Amount > 100

Why wrong: This is incorrect because the query does not return individual sales amounts; it returns aggregated totals for each Year and Category combination. The Amount > 100 condition is a filter on the sales rows that contribute to those totals, not a product attribute filter. The output granularity is Year-Category, not per-sale or per-product, so it cannot produce sales amounts for specific products.

The query returns total sales for each category, ignoring year

Why wrong: This is incorrect because Year is also a grouping column in the SUMMARIZECOLUMNS call, so the result retains a separate row for each year within every category rather than aggregating across all years. To ignore Year, the query would need to omit Year from the GROUP BY columns and possibly use CALCULATE to override the year filter context. Additionally, the Amount > 100 predicate restricts the underlying data, but it does not change the fact that Year remains part of the grouping granularity.

The refresh schedule uses UTC but the data source is in a different time zone.

Why wrong: The scheduled refresh time is expressed in UTC, and Power BI will accurately trigger the refresh at that moment regardless of the data source's local time zone. A time-zone mismatch only shifts the effective execution time relative to the source's clock; it does not affect connectivity or authentication. The error in this scenario is a credential failure, which is unrelated to time-zone conversion. Therefore, this option cannot explain the failed refresh.

The dataset has DirectQuery enabled, which prevents Import mode refresh.

Why wrong: This dataset is explicitly configured with Import mode and DirectQuery is disabled, as stated in the scenario. Even if DirectQuery were enabled, it would not 'prevent' Import mode for the whole dataset—Power BI supports a mixed storage mode where some tables use DirectQuery and others use Import. The process of scheduled refresh is only relevant to Import-mode tables, so DirectQuery's presence or absence is not the root cause. The actual problem is the unsupported SSPI credential type, not the storage mode.

The gateway ID is incorrect.

Why wrong: An invalid gateway ID would generate a distinct error such as 'Gateway not found' or 'The gateway is not configured' during the refresh attempt. In this case, the gateway ID is correctly provided, and the error message points to an authentication failure within the data source. The gateway's role is to broker the connection, but it cannot resolve an SSPI credential because that requires an interactive user. Hence, an incorrect gateway ID would not produce the specific SSPI-related error described.

All relationships are equally efficient

Why wrong: This is incorrect because relationship efficiency is directly affected by filter direction and cardinality. Unidirectional relationships are generally cheaper than bidirectional ones, which require the storage engine to propagate filters in both directions, raising query processing cost. Additionally, a relationship's status as active/inactive and its cardinality (one-to-many vs. many-to-many) further change performance. Therefore, the Product–Category bidirectional relationship is clearly less efficient than the unidirectional relationships in this model, disproving the idea that all relationships perform equally.

The relationship between Sales and Customer

Why wrong: The Sales-to-Customer relationship is a conventional many-to-one relationship with unidirectional cross-filtering, so filters flow only from Customer to Sales without any reverse propagation. This directionality is computationally efficient because the engine doesn't need to traverse back-reference paths or maintain extra join indexes. In normal reporting, there is no need for Sales to filter Customer, so this relationship doesn't introduce ambiguity or slowdown. Hence, it is not the relationship causing performance concerns in this scenario.

The relationship between Sales and Product

Why wrong: The Sales-to-Product relationship is unidirectional, meaning Product filters Sales but not the other way around, and that is the standard, efficient design. It doesn't directly cause performance issues because the engine only has to follow a simple many-to-one lookup path. The real bottleneck appears upstream, where the Product-to-Category relationship is bidirectional; that relationship causes filters to bounce back and forth and cascades into the Sales table. So even though Sales–Product participates in the filter chain, the inefficiency originates from the Product–Category relationship, making this option incorrect.

The measure should use ALLEXCEPT instead of ALL.

Why wrong: ALLEXCEPT would keep other filters but still remove date filter; need to keep date filter.

The VAR SelectedDate should be MIN instead of MAX.

Why wrong: MIN vs MAX does not change the issue; both return a single date.

The relationship between Sales and Date is inactive.

Why wrong: Inactive relationship would cause no filtering, but measure uses Date column.

The server name contains a hyphen, which is invalid in SQL Server connection strings.

Why wrong: Hyphens are allowed in server names; the name is valid.

The CommandTimeout value is too low and may cause queries to time out.

Why wrong: 600 seconds is 10 minutes, which is reasonable; the default is 600 as well.

The option 'CreateNavigationProperties' is set to false, which may prevent relationships from being created.

Why wrong: This option only affects the navigation properties in the data model, not relationships defined in Power BI.

The CALCULATE function removes all filters by default.

Why wrong: CALCULATE does not remove all filters by default; it only modifies the filter context based on its filter arguments. Unconditional removal occurs only when you explicitly specify functions like REMOVEFILTERS or ALL within CALCULATE. Without those, all other filters from the slicer remain active unless the filter argument directly targets the same table or column. Thus, the statement misunderstands CALCULATE's default behavior, leading to an incorrect diagnosis of the measure's issue.

The measure should use ALL(Sales[Date]) to respect slicers.

Why wrong: Using ALL(Sales[Date]) would remove filters from the Date column entirely, suppressing the slicer's filter context rather than respecting it. The ALL function is designed to clear filters, so it would make the measure ignore slicer selections even more aggressively. To respect slicers, you should omit any filter argument on that column or use FILTER with KEEPFILTERS to intersect rather than replace the existing filter. This option is the opposite of the correct approach.

The DATE function syntax is incorrect.

Why wrong: The DATE function in DAX takes year, month, and day as numeric arguments, and the syntax in the measure follows this pattern correctly. For example, DATE(2023, 1, 1) is valid and returns a date. Therefore, the DATE function is not the source of the problem. The issue stems from how CALCULATE and FILTER manipulate the filter context, not from any error in the DATE function's syntax.

The notify option should be set to 'OnFailureOrSuccess' to get alerts.

Why wrong: Notify option does not cause failure.

The refresh time is outside the allowed window for Premium capacities.

Why wrong: Premium capacities support scheduled refresh at any time.

The dataset is in a Premium capacity, which does not support scheduled refresh.

Why wrong: Premium capacities support scheduled refresh.