Microsoft Power BI Data Analyst PL-300 (PL-300) — Questions 1–75

Creating a separate Time dimension table with a TimeKey (for example, an integer 0–86399 representing seconds since midnight) and attributes such as hour, half-hour slot, and time band, then relating it to the fact table via a one-to-many relationship, gives every measure a consistent, reusable way to slice and drill by time of day. This follows star-schema best practice: the dimension stores the descriptive time attributes, the fact only stores the key, and users can navigate from hour to minute or across time periods without custom DAX. It fully supports the retail requirement of analyzing sales by hour while integrating cleanly with the date dimension.

Incremental refresh creates named date/time range partitions on a table and, during refresh, loads only new or modified partitions (e.g., the last N days) rather than the entire table. By using a 'LastModifiedDate' column, the refresh engine can detect which rows have changed and only refresh those historical partitions, dramatically reducing refresh time and data transfer. This requires using a Refresh Policy in Power Query (e.g., RangeStart and RangeEnd parameters) and the table must have a date/time column that records when each row was last modified.

The correct approach is to disable 'Share content with external users' in the tenant settings, because this switch directly governs whether users can share dashboards and reports with external email addresses. When turned off, the Share dialog rejects external recipients entirely, blocking both direct sharing and app access via external users. This is the primary tenant-level control for preventing outside access to dashboards without disabling internal collaboration.

If the relationship between the date table and the fact table is inactive, the date table's columns will not automatically filter the fact table because an inactive relationship is ignored during normal filter propagation. A visual that uses a date hierarchy from the date table will still display dates, but the measure values will be evaluated without that date context—often showing the grand total or a blend of all dates—so the results appear correct in shape but are numerically wrong. The only way to apply the filter is to explicitly activate the relationship in DAX using USERELATIONSHIP, which the user has evidently not done.

FILTER returns a table that contains only the rows from its first argument that satisfy the Boolean condition provided as the second argument. This table-returning behavior makes it a valid DAX function for creating a calculated table, such as a filtered subset of a fact or dimension table. For example, FILTER('Sales', 'Sales'[Amount] > 1000) yields a table that can be stored as a calculated table.

Using Split Column by Delimiter with '-' assumes the Date column contains text values such as '2025-04-09'. In the Power Query editor, select the column, choose Split Column > By Delimiter, specify '-' as the custom delimiter, and split 'Each occurrence of the delimiter' to create three separate columns. After splitting, you should rename those columns to Year, Month, and Day and change their data types to Whole Number or Text as appropriate. This is a direct and effective method when the format is consistently ISO-like.

The credentials used in the Source step are the root cause: the 'Login failed' error indicates the authentication attempt to the underlying data source was rejected. In Power Query, each data source stores its own credentials, and during refresh the engine retrieves them and passes them to the provider; if they are wrong, expired, or lack the required permissions, the connection fails immediately. This error surfaces before any subsequent query steps execute, so correcting or re-entering the credentials in the data source settings is the appropriate resolution.

The Values well in a scatter chart is reserved for numeric measures for the X and Y axes. Dropping DonorType there causes Power BI to treat it as an aggregated value (or a nonsensical measure) rather than as a categorical series selector. To get separate points for each donor type, DonorType must be placed in the Legend well, which creates a distinct series for each category. This misplacement is the exact reason the scatter shows a single cluster instead of separated groups.

Creating a role with a DAX filter that uses USERPRINCIPALNAME() and LOOKUPVALUE allows dynamic row-level security. The filter compares the category of each row to the categories assigned to the current user in the UserCategories table, ensuring users only see authorized data.

Microsoft Defender for Cloud Apps session policies use Conditional Access App Control to intercept Power BI sessions in real time. An admin can create a policy that triggers the 'block download' action when a session meets conditions such as a high-risk sign-in or a report containing a specified sensitivity label. This occurs at the proxy layer, so it neither disables the Export button in the UI nor depends on the user being a guest—explaining why the export is blocked for this specific scenario.

Conditional Access is an Azure AD feature that evaluates signals such as group membership, device compliance (via Intune), and risk before issuing a token for cloud apps like Power BI. By creating a policy that requires the user to belong to a group representing training completion and that their device be marked as compliant, the administrator can block access to all Power BI reports at the authentication layer. This is the correct approach because it combines identity and device context in a single, centrally managed policy, which works across web and mobile clients.

A one-to-many relationship requires the one side to contain unique values; if the products table's subcategory key repeated, the relationship could not be created as one-to-many and would either fail or become many-to-many. Uniqueness on the one side guarantees correct filter propagation from Subcategory to Products and onward to Sales.

Removing columns that are not used in the report reduces the data volume that must be evaluated, compressed, and stored in the VertiPaq engine, directly shrinking the model size and refresh duration. Every column, even an unused one, requires space for its dictionary and encoded segments, so eliminating irrelevant columns cuts memory overhead and can speed up query performance by reducing the number of column segments to scan. This practice also simplifies the query editor view and prevents accidental inclusion of sensitive or duplicate data in the model.

Calculated columns are computed during dataset processing and stored in the model, so they only recalculate when the dataset refreshes. Because the stem describes values not updating as underlying data changes, this explains the behaviour: the column reflects the last refresh, not live source edits.

Disabling the auto-date/time feature prevents Power BI from generating hidden date tables for every date column in the model. These hidden tables add significant memory and storage overhead, especially when you have many date columns. By turning the feature off, you reduce the model's size and refresh time, and you can instead create a single explicit date table if you need date logic.

A Date dimension table is essential for time intelligence in Power BI, as it provides a contiguous set of dates with attributes like year, quarter, month, and week. By marking it as the date table, DAX functions such as TOTALYTD and SAMEPERIODLASTYEAR can perform time-based calculations correctly. Without a dedicated Date dimension, filtering by fiscal periods or comparing periods across years becomes unreliable, especially if the fact table has gaps in dates.

This is the correct diagnosis: the gateway is running, but the SharePoint Online data source lacks stored credentials in the gateway's data source management settings. Without those credentials, the gateway cannot authenticate to SharePoint Online during a scheduled refresh, resulting in a failure. You need to add the data source and fill in the appropriate authentication method (e.g., OAuth2) to resolve it.

In a well-designed star schema, each dimension table contains unique key values (one row per member) and each fact table contains many transactional rows referencing those members. By setting the relationship as one-to-many from dimension to fact, you ensure that filters applied to dimension attributes propagate naturally down to the fact rows. This is the correct cardinality for a classic star schema, enabling fast, intuitive filtering and aggregations.

If one column contains only spaces (for example, a person's last name field holding `" "`), the concatenated result is a string composed entirely of whitespace. Because DAX does not automatically trim leading or trailing spaces in concatenation, the resulting column looks empty in visuals and may be treated as blank by later functions, even though it is not a true BLANK. This explains why the calculated column appears blank while actually holding a space-only value.

CALENDAR is a DAX table function returning a single-column date table, and calculated tables are built from DAX table expressions. This satisfies the stem's requirement for a valid calculated-table creation method, commonly used to generate a date dimension.

The YEAR function extracts the year from a date column and returns an integer. It is the correct function for this requirement. It works on a column reference and can be used in a calculated column. This is a straightforward and efficient way to get the year component from a date.

A line chart encodes data points along a continuous time axis and connects them with straight lines, allowing the eye to perceive direction, rate of change, and periodicity. Because the x-axis is continuous and time-ordered, Power BI can compute and overlay a trend line using linear regression, moving average, or other built-in analytics in the Analytics pane. It is the default and recommended visual for time-series trend analysis because it preserves the sequential structure of the data.

Integrated Security (SSPI) depends on the interactive user's Windows token to authenticate to the data source. In the Power BI service, scheduled refresh runs under a non-interactive service account, so there is no Windows identity to pass through. The service requires explicit stored credentials (e.g., SQL Server authentication or OAuth) for scheduled refresh, not SSPI. Because the data source is configured for SSPI, the refresh fails at the credential validation step.

DATESINPERIOD is correct because it directly returns the complete set of dates from an anchor date (typically the last date in the current visual filter) back across a specified number of intervals, such as -3 MONTH. This single function can be placed inside a CALCULATE filter to sum sales for the trailing three months, with the date range boundary handled automatically. Unlike the other options, it does not require manual date arithmetic (DATESBETWEEN), does not merely shift an existing set (DATEADD), and is not limited to one period (PREVIOUSMONTH).

A Power Query parameter, such as a date range or a scalar date value, can be referenced directly in the filter row step of a query. This makes the filter dynamic and easy to update without editing M code, and when the source is a SQL database, the filter step often folds into the generated SQL statement, reducing the amount of data imported. Parameterizing the date also supports scheduled refresh scenarios, where the filter value can be changed via the API or in the service, and it avoids hard-coded values scattered across steps.

Reducing the number of visuals on a DirectQuery dashboard directly reduces the number of separate DAX queries that Power BI sends to the underlying data source, because each visual in DirectQuery mode issues its own live query when rendered. Applying page-level filters constrains the rowset returned for all visuals on that page, which lowers the data volume and speeds up each query. Together these actions are a simple, immediate way to lower query load without changing the data model.

The denominator uses DISTINCTCOUNT of customers within each category's filter context. A customer buying three categories is counted once per category, inflating the numerator's revenue while the denominator stays low, so CLV per category exceeds the true customer-level value.

Applying a sort step followed by Keep Top Rows in Power Query is the optimal approach because the M engine can fold these transformations into a single SELECT TOP (N) ORDER BY statement on Azure SQL. This ensures that only the top N rows are fetched from the database, drastically reducing data transfer and load time. Additionally, the transformation remains declarative and reusable, and it aligns with best practices for source-side filtering in Power BI.

Configuring incremental refresh on the fact table is the correct approach because it partitions data by date using RangeStart and RangeEnd parameters, so only new and updated rows are loaded during each scheduled refresh. This dramatically cuts the data processed per refresh cycle, especially for a fact table accumulating transactional data over time. It requires at least one date column and enables the 'refresh policy' capability in Power BI to manage historical and current partitions separately.

A many-to-many relationship (for example, via a bridge table) does not provide a unique path for filter propagation between the two tables. When a measure evaluates a total, the storage engine must apply filters to both sides of the relationship, but because multiple rows can match on either side, the filter context becomes ambiguous and the engine may include duplicate or omitted rows. As a result, the detail rows for each record can appear correct, but the aggregated total becomes inflated or deflated because the same underlying row is counted multiple times or not at all. This ambiguity is a known limitation of many-to-many model relationships and directly explains incorrect totals.

The error indicates stored credentials expired or were changed, so refreshing them in the dataset settings restores authentication. Because the source uses Windows authentication, the user must re-enter valid credentials in the Power BI service, directly resolving the refresh failure before investigating gateways or permissions.

DATESINPERIOD is the correct time-intelligence function here because it returns a contiguous interval ending at MAX('Date'[Date]) and extending back 12 full calendar months, respecting month boundaries rather than fixed day counts. With -12 and MONTH, the filter context established by CALCULATE adjusts the Sales[Amount] summation to include exactly the trailing 12 months relative to the latest visible date, which is exactly what a rolling 12-month total requires.

A live connection means each Power BI visual executes a DAX query through the network to the Analysis Services instance, so physical distance directly affects response time. By co-locating the AAS server and the Power BI workspace in the same Azure region, round-trip network latency is minimized and data can stay within a single data center, which yields the fastest live query performance. This is a low-effort architectural fix that reduces latency without adding any per-query overhead or altering data granularity.

Rewriting the M script to embed filtering and aggregation in a native SQL query pushes all heavy lifting to the source database engine, which is optimized for set-based operations and can use indexes, statistics, and parallelism. Only the aggregated, filtered result set is sent to Power Query, drastically reducing data transfer and memory usage while also enabling full query folding so the output is computed server-side. This is the correct approach because it minimizes the data volume that Power Query must load and process.

Limiting columns to only those needed reduces the amount of data loaded into the model and the size of each query. In both Import and DirectQuery modes, cutting unused columns decreases memory consumption, storage, and network transfer, which directly lowers query execution time. This is a recommended first step for any performance optimization because it has no downside other than losing access to fields that aren't used.

Power BI map visuals delegate geocoding and boundary rendering to Bing Maps. When a report sends region names (e.g., states, provinces) to Bing, the service returns spatial coordinates and boundary polygon geometry; any inaccuracy, stale geo-dataset, or geopolitical ambiguity in that returned geometry directly produces the incorrect boundaries shown. Because the boundaries come from Bing's cartographic data rather than from Power BI or your data source, geocoding inaccuracies are the definitive root cause.

Dataflows are a valid choice for large data volumes because they perform transformations in the cloud using Power Query and can store processed output in Azure Data Lake, allowing you to reduce raw data size before it ever reaches a dataset. For example, you can filter, aggregate, or remove unwanted columns so the final dataset remains within Power BI's 1-GB Pro or larger Premium capacity limits. Dataflows also support enhanced compute and incremental refresh, which help process billions of rows without hitting the same bottlenecks as direct import.

Adding six months shifts July 1 to January 1 of the next calendar year, so Date.Year returns the fiscal year number. For example, July 1, 2023 becomes January 1, 2024, and Date.Year yields 2024, producing FY2024. This correctly labels the fiscal year starting July 1.

The Performance Analyzer in Power BI Desktop records the time spent on each stage of a visual's update, including DAX query execution, Visual Show time, and Layout time. By measuring specific visuals rather than guessing, you can identify the most expensive ones and apply targeted optimizations like rewriting DAX, simplifying the visual, or removing unnecessary record-level details. This evidence-based approach is essential because performance issues often stem from a few outliers, not the whole report.

Fact tables are the correct home for numeric, additive measures such as sales amount, quantity, or margin. These values represent the measurable business event at the grain of each fact row and are intended to be aggregated across dimensions. Placing measures in fact tables leverages Power BI's columnar storage and compression, enabling fast DAX calculations and efficient summarization. A well-designed fact table contains only foreign keys and numeric measures, keeping it narrow and high-performing.

Scheduling the refresh policy must align with the data warehouse's maintenance windows to avoid failures. During maintenance, the source database may be unavailable, have locked tables, or experience degraded performance, which can cause the incremental refresh to time out or return incomplete data. By scheduling refreshes outside these windows, you ensure source connectivity and consistent data loads, making this a valid design consideration.

SAMEPERIODLASTYEAR is a time intelligence function that requires a contiguous, continuous set of dates to shift the filter context back one year. If the 'Date' table is not explicitly marked as a date table (via the 'Mark as Date Table' setting) or is missing dates that exist in the 'Sales' table, the function cannot establish the previous year's date range and returns BLANK. Marking the table ensures Power BI validates that dates are unique and cover the full span of data, which is a prerequisite for reliable year-over-year calculations.

Correct because Table A (dimension) has unique values on the key column, and Table B (fact) contains many rows referencing that key. Power BI will filter Table B by selections in Table A. This is the standard star-schema relationship.

For drill down to work in a Power BI matrix, the fields placed on Rows must be part of a single hierarchy that defines the parent-child relationship between levels. When Category and SubCategory are added as separate fields, the matrix treats them as independent, always-expanded columns and does not show expand/collapse icons. To enable drill down, create a hierarchy in the Product table (e.g., Product Hierarchy: Category > SubCategory) and place that hierarchy on Rows. This is the necessary condition being tested.

Row-level security (RLS) is a valid method because it restricts data at the row level based on the identity of the signed-in user. By defining roles in Power BI Desktop and using DAX expressions (such as USERNAME() or USERPRINCIPALNAME()), RLS filters the underlying dataset dynamically, so each user only sees rows they are permitted to view. This filtering is enforced at query time, making it a robust, native access-control feature for Power BI datasets.

In the Navigator dialog, each worksheet and named table appears as a previewable source; choosing the 'Sales' sheet and clicking 'Transform Data' sends only that sheet's data into Power Query Editor as the single source for a new query. This lets you filter rows, change data types, or add custom columns before loading, while leaving every other sheet unread and out of the data model. This is the efficient, supported workflow for importing a specific worksheet.

Correct. Query folding indicators in Power Query Editor (a table icon with a down arrow or a fold icon) show whether a step is translated into native SQL and executed on the server. This directly tells you if transformations are being pushed to SQL Server.

SAMEPERIODLASTYEAR shifts the current filter dates back one year along the contiguous date column of the Date table. If that table is non-contiguous or does not include every day of the previous year, the function returns an empty set, making PreviousSales BLANK. Consequently, DIVIDE, with a blank denominator, yields BLANK despite a valid CurrentSales. This is the exact root cause.

Import mode loads and compresses the entire dataset into memory using the VertiPaq columnar engine, so query execution does not incur network round-trips to the source database. Aggregated queries such as SUM, COUNT, or GROUP BY run directly against this in-memory columnstore, enabling response times that are typically orders of magnitude faster than pushing the same aggregation to an external relational engine. In addition, shared aggregations get cached at the model level, which further accelerates repeated report interactions.

SUMX iterates row by row over the Sales table, so Sales[SalesAmount] and Sales[Discount] are evaluated in the same row. The COALESCE function replaces any null Discount value with 0, preventing null propagation and treating missing discounts as no discount. This correctly computes net revenue per row as SalesAmount * (1 - Discount), then sums across all rows, which matches the intended business definition of net sales.

Parameters allow you to change data source at deployment time.

In Power Query, the Split Column feature by a delimiter divides text into separate columns at each occurrence of a specified delimiter. For a FullName column containing comma-separated names, choosing comma as the delimiter splits it into two columns, with options to control the number of splits and how to handle extra delimiters. This directly achieves the goal of separating the name into distinct parts.

Every visual on a DirectQuery page issues its own set of DAX queries against the source, and filters from slicers/cross-filtering can cascade into additional queries. Reducing the number of visuals directly cuts the query count and the amount of data transferred, lowering page load time and easing the load on the data source while preserving performance.

The correct explanation is that this error is triggered during Power Query's type conversion when a value in the source table cannot be coerced to the expected DateTime type because it falls outside the valid date range (e.g., a year before 1753 or a calendar date like February 30). When the dataset definition expects a DateTime column and the underlying source contains an invalid date literal, the M engine raises an out-of-range or conversion error even though the source itself is accessible. This is distinct from a structural, connection, or syntax problem because the failure occurs during data evaluation/type handling, not during connection or parsing.

This is the correct star schema approach. Remove region and category descriptive text from the Sales table and replace them with foreign key columns (e.g., RegionID, CategoryID) that reference the primary keys of dedicated dimension tables. This normalizes the fact table, eliminates redundant string storage, and enables DAX filter and slicer operations to traverse relationships efficiently. It also simplifies future updates to dimension attributes without rewriting historical sales rows.

The 'Replace Values' feature in Power Query can replace null values with a specified text. This is a straightforward and efficient method to handle blanks in a single column. It does not require complex coding and is a common data cleansing step.

Append Queries is the correct operation because it stacks rows from multiple queries vertically, which is exactly what you need when combining three SharePoint lists that contain similar record types. When appending, Power Query matches columns by name, so after the append you may need to rename columns in the individual queries to ensure a uniform schema; otherwise you can end up with mismatched columns or nulls. This method preserves all source rows and yields a single consolidated table that is ready for further transformation.

Creating a calculated column that concatenates city and country (e.g., 'Austin, United States') gives the map visual a unique, geocodable location string. This directly resolves ambiguity because Power BI's Bing Maps geocoder treats the combined string as a specific place, avoiding mismatches like 'London' in the UK vs. 'London' in Ohio. It is a lightweight DAX solution that uses existing data, requires no external lookups, and is the intended best practice for ambiguous city names.

YEAR(Sales[OrderDate]) correctly uses the DAX YEAR function, which takes a date or datetime value and returns the corresponding four-digit year as an integer. Because Sales[OrderDate] is a date column, the function evaluates the date part of each row and yields a scalar numeric value, e.g., 2024. This is an efficient, type-safe way to create a Year column or measure for grouping, sorting, and filtering in Power BI.

Date is a classic dimension in a star schema because it provides the descriptive context necessary for time-based analysis, such as year, quarter, month, and day attributes. The Date dimension is placed in a separate table from the fact table to avoid repeating date attributes across fact rows, thereby reducing redundancy and enabling efficient filtering, grouping, and time intelligence calculations like year-to-date or period-over-period comparisons. Its role as a conformed dimension allows multiple fact tables (e.g., sales, orders) to share the same date dimension, making it a fundamental and correct choice for this design.

The Trim transformation removes leading and trailing whitespace from text values across the entire selected column. It modifies the existing column in place, which matches the requirement to avoid creating a new column. After trimming, the values can match the Students table and support a valid relationship. This is the standard data cleansing step for text keys.

ProductID is likely a numeric identifier. Storing it as a whole number instead of text reduces storage because numeric data types compress better and require less memory. This improves performance and reduces model size, assuming the values are indeed numeric and used as a key.

No gateway is required because SharePoint Online is a cloud data source that the Power BI service can connect to natively. During refresh, Power BI authenticates directly to SharePoint Online using the stored credentials (typically OAuth or an organizational account) and retrieves the Excel workbook through SharePoint's cloud APIs. The entire refresh operation runs in the cloud, eliminating the need for any on-premises gateway infrastructure or a local machine being online.

Changing the data type in Power Query Editor to Whole Number or Decimal Number is the proper and idiomatic remedy because it defines the column as numeric from the moment it enters the data model, immediately making it available for aggregations like SUM and AVERAGE. This transform is part of your ETL workflow, gets applied at each refresh, and is self-documenting in the query steps, ensuring downstream reports treat the column precisely as intended.

Because the report uses a live connection, every measure in the report must exist in the Azure Analysis Services tabular model; Power BI Desktop cannot author explicit measures locally in this mode. The correct approach is to add the measure as a calculated measure in the AAS model via SQL Server Management Studio (using the 'Calculated Measures' folder) or in Visual Studio's tabular model designer, then process the model. After refreshing the Fields pane, the measure becomes available for use in Power BI visuals.

DAX time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR require a continuous, contiguous set of dates and a proper filter context over a date column. A date table marked as a date table in Power BI Desktop fulfills this by providing an unambiguous date column and a relationship to your fact tables, ensuring the functions return accurate results. Without a marked date table, these functions may produce incorrect or missing values, especially when your fact dates are sparse or discontinuous.

This is the correct modeling pattern for a star schema: the Date table contains unique dates, while Sales has multiple rows for each date, so the relationship is many-to-one. With single cross-filter direction from Date to Sales, filtering by a date or a date hierarchy propagates to sales transactions, enabling efficient time-based aggregations. This unidirectional flow is unambiguous, standard, and performs well because it minimizes the filter context path.

SUMMARIZE evaluates a table expression over the Sales table, grouping rows by the Product[Category] column and computing the sum of Sales[Amount] for each distinct category. The result is a new two-column table (Category and Total Sales), with one row per category. This is the correct interpretation because SUMMARIZE is explicitly designed for grouped aggregation, not for altering the original table or its schema.

The folder connector's Combine Files operation reads every CSV in the folder, applies the shared table structure as a template, and appends all rows into one table, directly satisfying the requirement to merge identically structured files without manual per-file queries.

CALCULATE modifies the filter context so SAMEPERIODLASTYEAR can shift the current date range back one full year, returning the exact corresponding period from the prior year. DIVIDE then computes the percentage change as (Current - Previous) / Previous, and its third argument handles division by zero by default, preventing errors. This combination is the standard DAX pattern for year-over-year growth and is fully aligned with the requirement.

This is correct because Power Query's privacy-level engine prevents data from being combined across sources that have incompatible privacy levels, such as a 'Private' CSV file from a local folder and a SQL Server source with a different level. When the query attempts to fold or merge data from these two sources, the engine raises a privacy-level error unless levels are adjusted to allow the combination or a suitable firewall level is configured. The error is specifically triggered by the act of combining the sources, not by the nature of any single source.

Define row-level security (RLS) roles in Power BI Desktop by creating a role with a DAX filter—such as an expression using USERPRINCIPALNAME() or a mapping table—that filters the dataset to only the rows relevant to the signed-in user. After publishing, assign users or groups to the role in the Power BI Service, and the filter is automatically applied to all report consumers. This is the standard, supported approach for row-level access in a single dataset, and it works across DirectQuery and Import modes.

Using the 'List.Dates' function in Power Query M is the correct approach because it dynamically generates a continuous list of dates from a specified start date, a count of dates, and a step increment, all defined as parameters. You then wrap that list with Table.FromList or convert it into a column, allowing you to create a date table that automatically adapts to your data range without manual maintenance or external file dependencies.

Early filtering reduces data volume and improves performance.

Using an on-premises data gateway is the recommended approach because it securely bridges Power BI service to your on-premises SQL Server. Storing the SQL Server credentials in the gateway's data source settings keeps them encrypted and accessible only to gateway administrators, enabling scheduled refreshes without requiring individual users to have database permissions. This also supports row-level security mapping when needed, making it a secure and scalable solution.