Microsoft Power BI Data Analyst PL-300 (PL-300) — Questions 151–225

Filtering the source query by date restricts the import to current-year rows only, so Power Query loads a small subset rather than all 100 million rows. This reduces data volume at the source, which is the most efficient reduction technique.

This is the canonical SWITCH(TRUE()) pattern for multi-condition categorization. The first argument TRUE() is a literal value, and each subsequent argument is a Boolean condition; DAX evaluates them left-to-right and returns the result associated with the first condition that evaluates to TRUE. The final trailing value 'High' acts as the default/else clause because it has no accompanying condition. This syntax is both valid and efficiently conveys readable business logic in a calculated column.

Product is a classic dimension table. It contains descriptive attributes like product key, name, category, subcategory, color, and size, and is related to fact tables through a one-to-many relationship. Because it provides the contextual attributes used for slicing and grouping transactional data, Product belongs in the dimension layer, not the fact layer, and is correctly selected here.

A calculated column using SWITCH (or a lookup table) transforms the stored abbreviation into a full, canonical state name—for example, mapping 'CA' to 'California'—so the geocoder receives exactly what it expects. After the transformation, you can optionally set that new column's Data category to 'State or Province' to reinforce the mapping, but the key is that the underlying string has changed. This fixes the problem at the data layer, which is why it works regardless of the map visual type chosen.

Reports published to a shared workspace live in that workspace, not in 'My workspace', which is reserved for each user's personal content. The user must manually save a copy to 'My workspace' if they want a private version; otherwise, the report is only accessible from the workspace where it was published. This is the correct explanation for the user's confusion about the report's location.

Calculated columns are indeed created with DAX formulas, so this statement is true. You write an expression such as Amount * Rate in the formula bar, and DAX evaluates that formula once for each row in the table when the model is refreshed. The expression can reference columns from the same table, related columns in other tables, and basic DAX functions. Therefore, DAX is the required formula language for defining calculated columns in Power BI.

SUMX(Orders, Orders[Quantity] * Orders[UnitPrice]) is the correct way to compute total sales because it iterates over each row of the Orders table, evaluates the expression Quantity * UnitPrice for that row, and then sums all the row-level results. This preserves the row context and correctly handles varying quantities and prices across different orders. It is the standard pattern for row-by-row multiplication followed by aggregation, avoiding the pitfalls of using SUM with an expression or multiplying aggregates.

Microsoft 365 compliance portal surfaces the unified audit log, which captures Power BI activities including report views, dashboard shares and exports. Because the stem requires auditing those specific events, retrieving them here satisfies the requirement; the log aggregates Microsoft Entra ID-authenticated activity across workloads rather than Power BI service settings alone.

Updating the data source credentials in the gateway settings in Power BI Service is the correct resolution because the service uses those stored credentials every time it refreshes data through the on-premises data gateway. If the Oracle password changed, the account was locked, or the stored credentials simply expired, the refresh fails with authentication errors. Editing the data source in the service, re-entering the username and password, and re-testing the connection re-establishes a valid identity, allowing the refresh to succeed without altering the report or gateway installation.

A scatter chart is the only visual among these that plots two numeric measures as x-y coordinates, with each data point representing an individual observation. By showing the distribution, clustering, and trend of points, it directly reveals whether a relationship or correlation exists between the measures, and Power BI's analytics pane can overlay a trend line to quantify the strength and direction.

PREVIOUSMONTH is the correct answer because it is a dedicated time intelligence function that returns a table of all dates from the immediately preceding month, based on the current filter context. It automatically handles year boundaries, so when the current month is January, it correctly returns all dates in December of the prior year. This function requires a single column of contiguous dates and is typically used inside CALCULATE to apply the previous-month filter, making it the most direct and semantically accurate solution.

A slicer is an on-canvas visual control that displays a list of product categories and lets users click or multi-select values to instantly filter or highlight other visuals on the page. It remains visible as a persistent UI element, supports keyboard and assistive technologies, and allows ad hoc selection, making it the standard interactive tool for allowing users to highlight different product categories without pre-configuring every scenario.

The custom function uses Text.Trim and Text.Clean, which remove leading/trailing whitespace and non-printable characters only—they do not eliminate commas or currency symbols. When Number.From encounters a string like '$1,200', it raises an error because those characters are not part of a valid numeric literal in the default culture. The try-otherwise then returns null, so these unrecognized characters remain the true cause of the conversion failure. To fix it, you must strip commas and symbols (e.g., Text.Replace or Text.Remove) before invoking Number.From.

This is the correct action because marking a table as a date table explicitly tells Power BI which column represents the primary date and defines the table as the source for time intelligence functions. When a table is marked as a date table, Power BI validates that the column contains unique, continuous dates and uses it to enable functions like TOTALYTD, SAMEPERIODLASTYEAR, and RELATEDDAX date filtering. This designation ensures that relationships and calculations are aligned with the reporting date range.

The Size field well in a map visual controls the size of the bubbles based on a numeric value. Placing the Sales measure there will scale the bubbles proportionally to sales, making higher sales appear as larger bubbles. This directly achieves the requirement.

The Folder connector is the correct solution because it connects to a local directory, lists all files, and supports the 'Combine Files' feature in Power Query. With CSV files, it samples the first file to infer the schema, then automatically applies the same transformation to every file and appends the results into a single table. This is the standard approach for importing multiple CSV files with identical structures.

A conditional access policy in Microsoft Entra ID can target the Power BI service as a cloud app and apply a grant control that only allows sign-ins from specific security groups, while blocking all other users. This is the recommended identity-driven approach because Power BI authenticates via Entra ID, so the policy is evaluated during every sign-in, and it can also incorporate conditions such as device compliance, MFA, or location.

Adding the store name and sales fields to the tooltips bucket of the map visual is the correct approach because the tooltips bucket defines exactly which field values appear when a user hovers over a data point on the map. When you place fields there, Power BI automatically generates a hover tooltip that displays those values in a formatted layout. This is a per-visual configuration, so it directly affects the map without changing any other visuals or requiring navigation. Additionally, you can further format the tooltip text and group values to improve readability.

Marking a table as a date table requires a column of data type Date containing unique, contiguous values with no blanks. This uniqueness constraint is what Power BI validates, allowing the engine to treat that column as the authoritative calendar for time-based relationships.

Enabling drill-down mode and adding a Year and Quarter hierarchy to the matrix is the correct approach because it leverages Power BI's built-in hierarchy expansion and contraction. When the visual is in drill-down mode, the user can click the expand/collapse icons or right-click to move between Year, Quarter, and lower levels seamlessly, without changing pages or relying on static snapshots. This native functionality is exactly what the user needs for interactive level-by-level exploration.

Microsoft Purview Information Protection (MIP) auto-labeling policies can inspect report content for sensitive data patterns (e.g., credit card numbers or PII) and automatically apply the appropriate sensitivity label to Power BI assets. Because Power BI is deeply integrated with Purview, these policies run in the background and can label unlabeled reports, ensuring consistent protection without manual user action. This is a fully valid admin-centric enforcement approach.

Stacked area charts encode each category as a filled, colored region stacked above the previous one, so the vertical distance from one line to the next represents that category's value at each point. The top boundary of the stack shows the cumulative total over time, allowing you to read both the overall trend and each segment's contribution. This makes it the ideal choice when the goal is to visualize how individual categories combine to form a total across a continuous time period.

For a dataset in Power BI Premium capacity, you can schedule multiple refreshes per day (up to 48 per dataset), but each refresh must complete before the next one begins, and the service enforces a maximum refresh duration. To ensure data is loaded by 6:00 AM, you need to schedule the start early enough to account for the dataset's expected runtime, any queue time on the capacity, and potential competition from other datasets' refreshes. This directly addresses the core requirement of having fresh data by a specific time.

If the measure used in the card visual includes a function like ALL() or REMOVEFILTERS(), it ignores the filter context applied by the slicer. This would cause the card to display total sales regardless of the date filter. This is the most likely cause of the described behavior.

A relative date slicer is the correct interactive control because it lets report viewers quickly pick common relative ranges such as Last 30 days, This year, or Yesterday, and it updates dynamically as time passes. Unlike static filters or calculated flags, the slicer places a user-driven filter on the date field and works with any well-formed date column or date table. It requires no DAX and is the standard Power BI pattern for self-service date-range selection.

This is the only arrangement that correctly reflects the relationship between the two attributes and matches Power BI's drill-down semantics. Category is the broader grouping, and Product is the more detailed level, so the hierarchy starts at Category and descends to Product. When a user clicks the drill-down arrow, Power BI moves from the top level (Category) to the next level (Product), revealing the underlying data at the product granularity. This structure also supports roll-up (drill-up) back from Product to Category, which is the standard behavior in matrixes and charts.

USERELATIONSHIP is the DAX function designed to activate a specific inactive relationship inside a measure definition by taking two column arguments. It temporarily overrides the default active relationship for that measure only, leaving the model's active relationship intact for all other calculations. This is the standard pattern for handling multiple paths between the same tables, such as Order Date and Ship Date both referencing a Calendar table.

Removing unused columns from the fact table directly reduces the amount of data imported into the model. Each column consumes memory for storage and compression dictionaries; eliminating unnecessary columns lowers the overall model footprint and speeds up refresh and query times. This is a foundational data modeling best practice for minimizing model size without changing query behavior or architecture.

In Power Query Editor, selecting the column and setting the Data Type to Date invokes the type conversion engine, which parses each string value using the current culture's date formats. For a CSV with dates like '12/31/2024', this transformation changes the column's metadata to DateTime/Date type, enabling date-specific transformations, filtering, and accurate loading into Power BI. Since the column is currently text, this is the necessary step to ensure Power BI treats the values as actual dates for time intelligence calculations.

A line chart with a continuous date axis is the correct choice for displaying sales trends because Power BI preserves the date data type and plots each point based on its true chronological position on a time-scaled axis. With a continuous axis, missing dates are rendered as genuine gaps in the line instead of falsely connecting across them, accurately reflecting periods with no recorded sales. This configuration also supports time intelligence features, handles uneven intervals correctly, and lets viewers perceive the slope and direction of sales movement—exactly what a trend visual requires.

A relationship marked as inactive does not automatically propagate filters during evaluation. Even though a physical relationship exists between Sales and Date, it is bypassed unless the measure explicitly invokes USERELATIONSHIP (e.g., CALCULATE(SUM(Sales[Amount]), USERELATIONSHIP(Sales[DateKey], Date[DateKey]))). Because that activation is missing, the slicer on Date does not filter the measure and every date shows the same grand-total value.

Power BI Deployment Pipelines allow you to assign parameter rules (e.g., for data source parameters like Server and Database) that override values automatically when content is deployed from dev to test to production. This is the correct choice because it is the only option that truly meets the requirement of the report automatically using the production connection string without any manual adjustment. The rules are configured once in the pipeline and then applied consistently on every deployment, making the process repeatable and governed.

Importing both tables into Power BI loads the data into the VertiPaq in-memory columnar store, which compresses values and delivers sub-second DAX aggregations even without external infrastructure. Creating a one-to-many relationship on CustomerID between the Customers dimension table and the Orders fact table enables standard star-schema filtering—measures like 'Total Revenue by Customer' work automatically via row context and filter propagation, with no need for explicit joins. This design keeps the model compact, avoids data duplication, and is the recommended pattern for small datasets in Power BI.

The matrix visual is the correct choice because it is specifically designed to handle hierarchical data with row headers. By adding multiple fields to the Rows bucket, each row automatically displays an expand/collapse toggle (+/−) that lets users expand an individual parent to see its children while keeping the parent row visible. It also supports the full drill-down and drill-up toolbar buttons for navigating through hierarchy levels, giving users exactly the flexible, interactive hierarchy exploration needed.

Adding Salesperson to the matrix rows hierarchy below Region creates a two-level row hierarchy: each Region row gains an expand/collapse (+) icon, and users can drill down to see individual salespeople within that region. This is the only option that leverages the matrix's built-in drill-down capabilities, because Power BI interprets multiple fields in the Rows area as hierarchical levels, enabling interactive expand/collapse and also the drill-down/up icons on the visual header.

Reducing the number of visuals on a page directly reduces the number of DAX queries the Power BI engine must execute during initial load. Each visual, regardless of its size or complexity, generates at least one query against the dataset; eliminating a visual removes that query entirely. This cuts both query count and rendering overhead, delivering the most immediate improvement to page load time, especially when visuals are numerous and sourcing from large fact tables.

Removing columns in Power Query Editor before loading is the only true data-reduction method among the options. This action prevents the column's data from ever being imported into the model, reducing the .pbix file size, memory footprint, and refresh time. It also improves report performance because fewer columns are processed during queries. This is the recommended approach when a column is not needed for any analysis.

Promoting headers is a best practice because it replaces the default generic column names (Column1, Column2) with the meaningful labels from the first row, which then become the actual column names. Once promoted, you can reference these columns by name in Power Query formulas, making expressions more readable and maintainable. It also improves clarity for end users in the data model, since they see intuitive field names instead of placeholders.

In DAX, variables are evaluated at the point of definition within the current filter context, not at the point of use. When the measure runs for a single row, the filter context includes that row's OrderDate, so CALCULATE(MAX(Sales[OrderDate])) returns that same date—making PreviousDate equal to LastDate. To get the previous date, you need an explicit filter that alters the context, such as FILTER(ALL(Sales[OrderDate]), ...) or a relative time function like DATEADD. This is the classic variable-context pitfall.

This formula uses the IF function to check if the EnrollmentDate is greater than or equal to the date 30 days ago from today. If true, it returns 'New'; otherwise, 'Existing'. The TODAY() function returns the current date, and subtracting 30 gives the date 30 days ago. This correctly categorizes students based on the last 30 days and is a valid calculated column expression.

The 'Combine Files' transform is the correct approach because Power Query connects to the folder, reads a sample file to infer the schema, and then automatically applies the same transformation steps to every CSV in that folder, stacking them into a single table. It handles an arbitrary number of files, is refreshable when files are added or removed, and is the native, scalable pattern for importing multiple CSV files with identical structures.

SAMEPERIODLASTYEAR returns a contiguous set of dates shifted exactly one year back from the current filter context, preserving the precise date range of the reporting period. This makes it possible to compare, for example, current month-to-date or quarter-to-date sales directly with the same calendar segment in the previous year, which is the foundation of a year-over-year growth measure. For instance, the measure YoY Growth = DIVIDE([Sales] - CALCULATE([Sales], SAMEPERIODLASTYEAR(Date[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR(Date[Date]))) yields the required percentage change.

The Quick Measure for 'Running total' automatically generates a measure that uses CALCULATE with FILTER and ALLSELECTED, capturing the current date context via MAX(Calendar[Date]) and summing values for all dates less than or equal to that point. This approach is fully dynamic: it respects slicers and filters on other columns, updates with the selected date range, and is a best practice because it leverages a proper date table and measure semantics. Unlike a calculated column or table, the generated measure is a lightweight, query-time computation that responds instantly to user interactions.

Relationships require columns, not measures.

Use Power Query at the data preparation layer to apply transformations such as Trim, Proper, and Replace Values, or merge a supplementary mapping table into the dimension to consolidate spelling variants. By standardizing region names before the data is loaded, the dimension table contains a single canonical row per region, enabling correct one-to-many relationships and accurate aggregations in the star schema.

The Power BI activity log records granular events including ShareReport, ShareDashboard and CreateDashboard, retrievable through the admin portal or REST API. It satisfies the audit requirement by capturing who shared what, with whom, and when across the tenant.

A dedicated mobile layout view is a Power BI feature that provides a separate phone-optimized canvas (portrait orientation) where you can reorder, resize, and hide visuals specifically for small screens. Unlike automatic reflow, this layout lets you control exactly what appears, ensuring touch-friendly spacing and prioritized content on each page. It is a best practice because it guarantees a deliberate mobile experience rather than a haphazard shrink of the desktop layout.

Replace Values -> Replace null with a default value is the correct approach because it directly modifies the ProductID column, converting each null into a specified default such as 'Unknown' or '0'. This preserves all rows while guaranteeing that no empty ProductID values remain, which is exactly the requirement. Power Query's Replace Values operation supports replacing nulls specifically, making it a targeted and effective data-cleaning step.

The Q&A visual is correct because it is specifically designed to let users type natural-language questions directly into a visual on a report or dashboard. It uses the semantic model's fields, measures, and relationships to interpret the question and automatically generate the appropriate visual, such as a bar chart or a table. This matches the task of allowing users to ask questions about their data in an ad-hoc, self-service manner.

ALL(Product[Category]) inside CALCULATE removes only the active filter on the Product[Category] column, while all other filters in the current context (such as date, region, or store) are preserved. This gives a denominator that represents total sales across all product categories within the same filtering context, which is exactly the correct baseline for computing a category's percentage. DIVIDE also safely handles division by zero by returning BLANK instead of an error, making this the most accurate and robust option.

Append Queries stacks tables with matching columns vertically, combining the identically structured regional sheets into one table. This satisfies the requirement to consolidate all sheets into a single table for analysis without altering the underlying column structure.

The error message indicates that the measure is not a valid scalar value. When a measure references a column without an aggregation function, such as Sales[SalesAmount] directly, or returns a table expression, SUMMARIZECOLUMNS fails because it expects a scalar for each column in its result set. Ensure the measure aggregates the underlying column using SUM, AVERAGE, or another scalar-returning function. Additionally, verify the measure is not using a calculated table or a row context that produces multiple results.

Power BI activity logging captures all user interactions with datasets, including views, exports, and sharing, and can be streamed to an Azure Log Analytics workspace for centralized analysis. Once in Log Analytics, you can write KQL queries to filter activity records by IP address ranges and configure alerts to trigger when access originates from external IPs. This approach fully satisfies the requirement to log all dataset access and proactively monitor for suspicious external access in a shared capacity environment.

Data bars and colour scales encode each cell's magnitude visually, so regional and category patterns stand out without adding rows or columns. This directly reduces the matrix's clutter, the stated readability problem, while preserving all existing values.

Azure AD B2B collaboration allows sharing with external guests.

Text.Select extracts only the characters specified in the list, in this case digits 0 through 9. This yields the numeric portion as text, which can then be converted to Whole Number. This approach is flexible and handles varying letter prefixes. It is a robust way to isolate numbers from alphanumeric strings.

Setting tab order on visual elements creates a logical keyboard-only flow through a report. This is a core WCAG 2.1 requirement under 'keyboard accessible' because it lets users navigate without a mouse. By defining a sequence, Power BI ensures screen reader users and those with motor impairments can reach every interaction predictably, avoiding random or confusing jumps.

Using the SharePoint folder connector is the correct approach because it connects to a library folder and returns a table with one row per file, including a Content column that stores the raw binary data of each Excel workbook. After filtering that table to .xlsx files, you expand the Content column, which invokes Power Query's Excel parser to load each workbook and list all its worksheets. Applying a filter on the sheet name to 'Sales' ensures that exactly the sheet you need is combined across all matching workbooks, making this the native and reliable method for importing multiple Excel files from a SharePoint folder.

FILTER is a table function that takes a table and a logical condition, and returns a subset of rows for which the condition evaluates to TRUE. It returns a table object, preserving the original columns and establishing row context for each row, so it can be used as a table expression or as a filter argument in CALCULATE. This makes FILTER a valid DAX function for creating table results.

This measure uses SUMX to evaluate the expression row by row, and COALESCE converts any BLANK discount into 0 so that missing values are handled gracefully. By using (1 - COALESCE(Orders[Discount], 0)), rows without a discount contribute their full quantity × unit price, while rows with a discount are reduced exactly as intended. This is the correct and robust way to account for nullable discount columns in a fact table.

Drillthrough pages are designed for cross-level navigation: the source visual defines a drillthrough field, and when a user right-clicks or clicks with drillthrough enabled, Power BI passes that field's value as a filter to the destination page. This preserves the selected data point's context automatically, allowing the detail page to show only related information while executives start from a clean KPI overview. A back button returns them to the originating page, giving a fluid, context-aware drill-down workflow that bookmarks and tooltips cannot match.

This is correct because PatientKey is the unique surrogate key in the Patients dimension and Encounters[PatientKey] is the foreign key. A one-to-many relationship from the dimension to the fact table enforces that each encounter relates to exactly one patient record, regardless of how many MRNs that patient has.

This is the correct configuration because the KPI visual requires all three: Value for the current metric (e.g., current sales), Trend axis for the time series that drives whether the metric is trending in the right direction, and Target goals to define the benchmark against which current performance is measured. With these three bound, Power BI can calculate the KPI state, show delta versus target, and render the trend sparkline plus the color-coded status indicator.

DirectQuery translates report visuals into SQL queries executed in real time against the data warehouse, so the source system's query cost is the core bottleneck. By optimizing the SQL—reducing unnecessary joins, pushing predicates and aggregations to the source—and adding appropriate indexes on foreign keys, date columns, and frequently filtered columns, you directly lower query execution time. This is the most effective way to improve report responsiveness without leaving DirectQuery mode.

A line chart is the optimal choice for visualizing customer fee trends because it maps a continuous time field to the category axis and the fee measure to the value axis, using position and slope to encode magnitude and direction. It naturally reveals upward or downward movements, fluctuations, seasonality, and inflection points, and Power BI's date hierarchy supports multiple granularities (day, month, quarter, year) without changing the visual. Its perceptual efficiency for comparing positions along a common axis makes it the standard for time-series data.

Inner join is the correct choice because it returns only rows where the join key exists in both the Orders and Customers tables. This effectively filters the data to matching records, which is typically the desired outcome when merging transactional data with dimension tables. In Power Query, inner join is the default and guarantees that no unmatched rows are included, ensuring referential integrity in the merged result.

This is exactly what the role's DAX filter does. When a user maps to a role with a table-level filter like [Region] = 'US', Power BI applies that predicate to every query against the Sales table, returning only matching rows. All other rows are filtered out at the data source level (or in-memory during import), so the user's report and any aggregations are based only on US sales. This is the intended behavior of RLS.

List.Dates is the correct M function because it is specifically designed to generate a sequential list of date values. Its signature, List.Dates(start as date, count as number, step as duration), creates a contiguous series by incrementing the start date by the provided duration each time. For example, List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)) returns a list of all 366 days in 2020, which can then be converted directly into a date dimension table. This makes it the most direct, readable, and type-appropriate choice for this requirement.

This star schema design is optimal because Department and Account become conformed dimensions that can filter both fact tables without duplicating attributes. Budget and Actuals remain separate fact tables, enabling measures like SUM(Actuals[Amount]) and SUM(Budget[Amount]) to be compared directly using DAX, with relationships enforcing correct row context. This setup preserves referential integrity, supports drill-through, and lets the engine optimize query performance by navigating relationships rather than scanning stacked data.

A Microsoft Purview DLP policy scoped to Power BI with a sensitive information type detecting credit card numbers enforces the block at export time. This satisfies the requirement because enforcement follows the data itself, regardless of which workspace or report contains it.

Consistent color coding across visuals is a core pre-attentive design principle: it lets executives immediately map colors to the same business dimension or measure without re-reading legends. When a fixed category color palette is applied through the theme or model, it reinforces data shape and supports quicker comparisons across pages. This consistency also reduces errors when users transition between charts, because their learned color associations transfer intact.

This is the correct answer because this relationship is the one configured with bidirectional filtering, a configuration known to degrade query performance. With bidirectional cross-filtering, a filter on Category flows to Product and then to Sales, but also filters on Product or Sales can propagate back to Category, causing extra dependency chains and potential ambiguity in filter context. This forces the query engine to evaluate additional row combinations and can make the model significantly less responsive. In contrast to unidirectional relationships, this bidirectional flow creates unnecessary complexity, so the Product–Category relationship is the least efficient.

DISTINCTCOUNT(SurveyResponses[RespondentID]) is correct because it evaluates the unique values in the RespondentID column, ignoring blanks and returning the number of distinct respondents. In a table where each row is a survey response, a single respondent may appear multiple times; distinct counting on the respondent identifier isolates each unique individual, which is exactly the measure needed. This function is designed for counting unique non-blank values and is the standard way to count distinct entities in DAX.

The dataset owner is the correct recipient because MailOnFailure is a dataset-level property that triggers an email to the owner's Power BI account when a scheduled refresh fails. This notification is tied to the individual who originally created or has explicit ownership of the dataset, not to any broader group or role. Even when other users edit the dataset, the owner remains the fixed point of contact for the dataset's operational alerts, such as refresh failures.

Splitting a column by the space delimiter in Power Query is a native M transformation that invokes the Splitter.SplitTextByDelimiter function behind the scenes, generating two separate columns for first and last names in a single step. This declarative approach is optimized for large volumes of rows, requires no manual mapping, and automatically applies to every row, making it the most efficient and maintainable solution among the choices.