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

To see the monthly sales value on hover, ensure the line chart's tooltip is enabled in the Format pane and that the Sales measure is included in the Tooltip well (it is by default). When configured, hovering a point shows a tooltip displaying the category, series name, and the measure value. If the tooltip is currently not appearing, verify the tooltip toggles are on and no report page tooltip has overridden it.

SAMEPERIODLASTYEAR is the precise DAX function for year-over-year comparisons because it returns a table of dates that exactly mirrors the current context's date range shifted one calendar year back. When used inside CALCULATE, it applies that prior-year date filter to evaluate the measure for the equivalent period, automatically handling year boundaries and leap years. This makes it the standard choice for calculating prior-year values in YoY growth formulas.

Publishing to web is the only option that bypasses all authentication and licensing requirements entirely: it generates a publicly accessible embed code and a URL that anyone with the link can open in a browser, with no sign-in or Power BI license needed. This exactly matches the requirement of letting an external user view report content without credentials, but it is also risky because the report is effectively published on the internet for anyone who discovers the link to see, including potentially sensitive data. In production, this feature is appropriate only for non-confidential, public-facing content, and an administrator should ensure the tenant-level 'Publish to web' setting is allowed before using it.

Replacing errors with null in Power Query is a non-destructive transformation that explicitly substitutes each invalid date value with a null while keeping the row intact. You apply it to the date column (Home > Replace Errors or context menu) and specify null as the replacement value. This preserves all other column values for that row and produces a clean, nullable date column that the data model handles naturally, e.g., through blanks in visuals or DAX functions like CALCULATE with filters. It also avoids the risk of load failure due to leftover errors.

This design correctly distinguishes the default business date (OrderDate) from an alternative date role (ShipDate). Because a table can have multiple relationships but only one active, the ShipDate relationship is configured as inactive, meaning it won't affect normal filters. When shipping analysis is needed, a DAX measure can use CALCULATE with USERELATIONSHIP(ShipDate[Date], Date[Date]) to activate that relationship for the duration of the calculation, preserving both analytical paths without ambiguity.

Field parameters, created in the 'Parameters' pane in Power BI Desktop, let you define a list of DAX expressions (e.g., SUM(Sales), SUM(Profit)) and expose them as a single slicer or dropdown. When a user selects a value, that DAX expression is passed to a visual, dynamically swapping the measure or field being displayed. This is the correct solution because it is purpose-built for dynamic field/measure selection, avoids multiplying visuals, and works seamlessly with report themes and active relationships.

The LOOKUPVALUE function performs an implicit filtering scan over the target table for every row in the fact table, often resulting in an O(n×m) operation where n is the fact row count and m is the lookup table size, causing severe performance degradation in large models. Creating a relationship between the tables lets the VertiPaq engine exploit its in-memory columnstore dictionaries and B-tree-like indexes, enabling hash-based or bitmap-based lookup propagation that is dramatically faster and more memory-efficient. This is the recommended alternative because a relationship is declarative metadata that the query engine can optimize, whereas LOOKUPVALUE is a procedural row-by-row DAX function that prevents such optimization.

OAuth2 tokens are issued with a limited lifetime (usually 1 hour to 90 days depending on the provider) and are used for authentication when connecting to cloud data sources. When a token expires, the Power BI service cannot refresh the dataset automatically because it lacks the ability to prompt for reauthentication. A manual refresh opens a dialog that lets the dataset owner re-authenticate, renewing the token and successfully completing the refresh.

Assigning the workspace to a Premium capacity ensures that the workspace uses the dedicated resources of that capacity, which is required for features like larger datasets and increased refresh rates. This action is necessary to meet the requirement of using a specific capacity. It is done through the workspace settings in the Power BI service, where you can select the capacity from a dropdown list.

SAMEPERIODLASTYEAR is the correct time intelligence function for year-over-year comparisons. It takes the current filter selection — whether it is a single date, a month, a quarter, or a custom range — and returns the exact corresponding date range from the previous calendar year, preserving the number of days and the shape of the selection. This precision makes it the standard DAX approach for computing previous-year totals, especially when you need the prior year to match the current period on a day-for-day or period-for-period basis.

Pushing the OrderQty > 10 predicate into the SQL query lets SQL Server filter rows before transfer, so Power Query receives fewer records. This reduces data shuffled across the connection, satisfying the performance constraint. Filtering after import forces the full dataset through the gateway, wasting bandwidth and memory.

In a composite model, relationships between tables from different storage modes are the only way Power BI can join and aggregate data across sources at query time. A correctly defined relationship—with proper cardinality and filter direction—ensures that cross-source measures, such as a SUM over an imported table filtered by a DirectQuery table, produce accurate results. Without those relationships, the engine cannot determine how to combine rows, causing errors, blank values, or meaningless totals.

OrderID is a natural business key that identifies an order, not a numeric measure or a surrogate foreign key. In a star schema, natural keys and descriptive attributes should live in the appropriate dimension table, such as an Order dimension, so that the fact table contains only relationship keys and measures. Including OrderID directly in the fact table violates star schema normalization and reduces maintainability. Thus, OrderID is the item that should NOT be placed directly in the fact table.

Removing unnecessary columns in Power Query before loading is the most efficient method because it reduces the number of columns imported into the VertiPaq in-memory engine. Power Query transformations that discard columns are applied during the read/load cycle, so the CSV parser only passes the selected columns to the model, reducing memory usage, disk footprint, and refresh time. This early reduction also improves compression ratios and query performance across reports that reference the table.

Creating a separate Product dimension table with attributes like category and linking it to the Sales fact table through a ProductID relationship is the canonical star schema design. This separates descriptive attributes from numerical measures, enabling efficient slicing, filtering, and drill-through while eliminating redundancy. The one-to-many relationship also allows DAX to propagate filter context automatically from the dimension to the fact table, which is fundamental for correct aggregation and better query performance.

A star schema separates the central fact table (numeric, measurable data like sales amounts and quantities) from surrounding dimension tables (descriptive attributes like products, customers, and dates). This structure lets Power BI compress columns efficiently and minimizes the number of join paths, so DAX measures filter and aggregate quickly. Because each dimension is directly linked to the fact table, the model becomes intuitive for self-service users and provides clear filter context for slicers and visuals.

Choosing 'Split Column by Delimiter' with the 'Right-most delimiter' option is correct because it targets the final space in the name, separating the last name from all preceding text. This works reliably for variable-length strings because it does not depend on character counts or the number of spaces; even 'Mary Ann Jones' splits into 'Mary Ann' and 'Jones' as two columns. Power Query executes this by treating the last delimiter occurrence as the split point, which is exactly what is needed to isolate the surname.

Scatter charts support constant lines on both the X and Y axes. By adding a constant line on each axis at the average cost and average defect rate, you create a quadrant view. Points in the upper-right quadrant represent production lines with above-average cost and above-average defect rate, exactly what you need.

Keeping TransactionID in the fact table is correct because it acts as the natural key for each transaction fact row, preserving row-level identity without creating a separate dimension. As a degenerate dimension, it supports direct row references and enables critical operations like incremental refresh, audit trails, and error reconciliation. It also lets you uniquely identify rows when the fact table has no other unique key, even if you need to combine it with a line number at a lower grain. This approach follows Microsoft's star schema guidance and avoids unnecessary joins or duplicated data.

Snowflake supports DirectQuery.

The 'externalSharingEnabled' setting is the tenant-level switch that directly controls whether users can share dashboards and reports with individuals outside the organization. When disabled, the Share option for external email addresses is blocked, and any existing external shares become inaccessible. Because the question describes an inability to share with external users, this disabled setting precisely explains the behavior and is the correct answer.

A well-formed star schema requires each dimension table to have a primary key (surrogate or natural) that uniquely identifies every row, paired with descriptive text columns such as product category or region name. These descriptive attributes provide the context for slicing and dicing in Power BI, while the primary key ensures referential integrity from the fact table and enables efficient row reduction during query execution. Without a unique key, filter propagation from the dimension to the fact becomes ambiguous and can produce duplicate or misleading results.

The Member role in a Power BI workspace grants the ability to manage permissions—adding or removing members, assigning roles, and editing content—without allowing workspace deletion. This precisely matches the requirement to manage permissions while retaining the workspace. Therefore, Member is the correct assignment.

DATEDIFF computes the number of interval boundaries crossed between two dates, with the third argument specifying the unit (here DAY). It returns a whole number representing the difference between OrderDate and ShipDate in days, which is exactly the shipping duration. This function is appropriate because it directly compares the two relevant columns and is a standard date difference function.

Product category is descriptive, non-additive text used for slicing sales measures, so it belongs in a dimension. Fact tables hold numeric, aggregatable transaction data such as quantity and revenue, making category the correct dimensional attribute.

Removing duplicate rows from the CustomerID column in the dimension table guarantees that each customer appears exactly once, which satisfies the uniqueness requirement for the 'one' side of a one-to-many relationship. In Power Query, you can select the CustomerID column and click 'Remove Duplicates' to keep the first occurrence and delete the rest. This creates a clean dimension that can be joined correctly to the fact table via a relationship.

Power Query's Date/Time/Timezone type expects an offset such as +00:00 rather than a Z suffix. Replacing Z with +00:00 preserves the UTC offset and allows the type conversion to succeed. This keeps the original instant intact while satisfying the parser's expected format.

This correctly models price as a Type 2 slowly changing dimension: a separate PriceDim table contains price, product key, and valid_from/valid_to dates, and is related to the fact table on product and date (or you use a DAX lookup to the correct row). When filtering by date or product, the model returns the exact price in effect for each transaction, preserving historical accuracy and enabling period-over-period price analysis. This design avoids data loss, supports semi-additive measures like quantity at price, and is the standard star-schema approach for time-varying product attributes.

Left Outer join (Join Kind = Left Outer) keeps every row from the first query—orders—as the left table, and appends columns from customers only when the join key (CustomerID) matches. For orders that lack a matching customer record, the added customer name column is null, but the order row remains intact. This is the correct choice because the business need is an order-centric view where all orders must appear regardless of whether customer reference data exists.

The first step in troubleshooting a refresh failure is to verify that the gateway is operational. If the gateway is offline or outdated, it cannot connect to the on-premises data source. Checking the gateway status in the Power BI service under 'Manage gateways' will reveal if the gateway is online and if an update is available. This is a common cause of refresh failures.

The 'From Folder' data source with 'Combine & Transform Data' is the native, dynamic solution. It prompts Power Query to generate a sample-file query, a transformation function, and a final combined output that automatically applies the same steps to every CSV in the folder. On refresh, newly added or removed files are reflected without manual intervention, and schema changes are detected and promoted automatically.

Calculated tables let you use DAX table functions such as SUMMARIZE, GROUPBY, or UNION to build a genuinely new analytical table from existing model data. The result is stored as a physical table in the Data pane, so it can participate in relationships, support measures, and be referenced in reports even when the source system does not contain that exact aggregation. Because it is fully materialized at refresh time, it persists as a reusable, pre-aggregated view.

A bar chart maps each category's value to a length along a common scale, typically anchored to zero, which is one of the most accurate visual encodings for comparing quantitative magnitudes. The aligned positions of bar ends allow users to see differences quickly and precisely, and Power BI can augment bars with data labels, sorting, and color to emphasize the largest category. For a simple 'users' breakdown by category, a bar chart directly answers the comparison question with minimal perceptual error.

A relative date filter on the date field set to 'Last 12 months' directly applies a dynamic query-time filter that includes exactly the trailing 12 complete months (or periods) relative to the current date. This filter automatically shifts as time advances, so the visual always reflects the most recent 12 months without requiring manual updates or user interaction. It is the correct approach because it operates at the filter level, ensuring every visual using that date field is restricted to the desired window while still allowing the underlying measures to calculate correctly within that context.

The 'Merge Queries' feature in Query Editor combines rows from two tables based on a common column, supporting join kinds such as left outer, right outer, full outer, and inner. You can expand the resulting columns to bring in related fields, which is a common way to enrich one table with data from another. This operation is performed in Power Query before the data is loaded into the model.

Split Column by Delimiter directly satisfies the requirement to separate 'FullName' into 'FirstName' and 'LastName' at the space character. Power Query's split transformation supports a space delimiter with options for leftmost, rightmost, or each occurrence, producing the two new columns in a single step.

Using latitude and longitude fields instead of location names is the correct fix because it bypasses Bing Maps geocoding entirely. When Power BI reads numeric latitude and longitude, it plots points directly at those exact coordinates, eliminating any ambiguity from store names, cities, or countries that might be misspelled, duplicated, or non-standard. This approach ensures each store appears at its precise physical location, and it is the recommended practice whenever your data source contains coordinate data. It also avoids the overhead and potential failure of geocoding requests, making the report more reliable and faster to render.

RLS rules in Power BI evaluate DAX expressions that return a Boolean. These expressions are evaluated per row in the table, and they can reference columns from the table (or related tables). A calculated column materializes a value for each row, so it can be directly referenced in an RLS rule like `[Region] = "West"`. This is a valid use because RLS predicates are row-context-based and cannot use measures that depend on user filter context. So a calculated column is appropriate.

Suggested Questions are automatically generated by the Q&A engine based on the model's tables, columns, measures, and any defined synonyms, and they appear as clickable chips at the top of the Q&A visual. They are a built-in part of the Q&A user experience, designed to help users get started and to demonstrate what kinds of NLQ the model can answer. Because they are produced directly from the same metadata that powers natural-language parsing, they are a feature inherently tied to NLQ support.

Bookmarks capture the current state of a report page, including which visual is visible. Assigning bookmarks to buttons lets users toggle between the bar chart and line chart views without editing the page, satisfying the dynamic switching requirement within a single report page.

Data labels display the exact numeric value directly on each bar, so administrators can read patient counts without hovering. This is the standard way to show precise values on a chart. Enabling data labels on the clustered bar chart provides the required information at a glance, improving usability for the intended audience.

Creating a DAX date table with CALENDAR or CALENDARAUTO generates a contiguous, minimally sized date column that covers the model's date range. Marking it as a date table enables time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) to work reliably against any related date column. This approach keeps the model lean because it contains only the date column and any explicitly added attributes, and it avoids the hidden auto-date tables that Power BI otherwise creates.

Creating a one-to-many relationship from Customer (one side) to Sales (many side) based on CustomerID is the correct star schema pattern. CustomerID is the unique primary key in the Customer dimension table, and it appears as a foreign key in the Sales fact table, allowing each customer to link to multiple sales transactions. This direction supports intuitive filter propagation from the dimension to the fact table, enabling reliable aggregations like total sales per customer while preserving the granularity of the fact table and maintaining a clean, normalized model.

The SharePoint Online Folder connector retrieves metadata and binary content for all files in a document library, and the 'Combine files' transform then generates a parameterized function that uses a sample file to infer parsing logic. During each data refresh, the folder query re-enumerates the library, so any newly added Excel file is automatically detected, read, and combined with the rest using that same function. This is the intended, scalable pattern for loading multiple similarly structured Excel files from SharePoint without manual query updates.

Correct. SUMMARIZECOLUMNS groups the sales data by the Year and Category columns, and the filter condition Amount > 100 is applied to the base table before aggregation. Therefore, for every distinct Year-Category pair, the query returns the sum of the sales measure computed only from rows that satisfy Amount > 100. This exactly matches the described output of total sales per year and category under that filter.

Since the Power BI dataset uses a live connection, it fully relies on the Azure Analysis Services tabular model for security enforcement. To make the measures visible to users, you must edit the object-level security roles in AAS and grant those roles read permission on the measure metadata objects that are currently hidden. This ensures that the measures appear in the field list and are accessible to queries from Power BI for the assigned users.

A conditional column built on the BillingPeriod values directly maps each known string to the required integer. After setting the new column's type to Whole Number, the model receives a numeric field suitable for calculations such as dividing annual revenue by twelve. This satisfies the requirement entirely inside Power Query without altering the OData source.

Defender for Cloud Apps activity policies are purpose-built for monitoring cloud app usage and can detect Power BI export activities in near real time. You can create a policy that filters on Activity type 'Export' and a sensitivity label of 'Highly Confidential' (via Microsoft Information Protection integration), then trigger a custom alert. This provides the precise alerting for data exfiltration events that the scenario requires.

Remove Duplicates is a row-level cleaning transform in Power Query that scans all selected columns and retains only the first occurrence of each unique combination, discarding subsequent identical rows. This directly reduces row count and eliminates redundant data that would otherwise skew aggregations, making it a genuine data-cleaning operation. Distinct from filtering or grouping, it requires no aggregation or merge logic—just deduplication based on key columns.

A separate date table is a best practice because DAX time intelligence functions like DATESYTD, SAMEPERIODLASTYEAR, and TOTALYTD require a continuous, contiguous date range and a table marked as a date table to work reliably. When you mark a dedicated date table, Power BI establishes the necessary relationship and ensures that time-based calculations respect the fiscal year and other custom calendars. Without it, time intelligence can return incorrect results due to missing dates or auto-generated date hierarchies.

Investigating the user's permissions on SalesDataset is the correct first step because the activity log shows only the outcome (a report created with a Confidential label), not the authorization chain. The analyst should check the user's effective permissions through workspace roles, dataset sharing, and row-level security, using the lineage view or the 'Manage permissions' page. Only after confirming whether the user is allowed to see the underlying data can you decide to take action, thereby adhering to the principle of least privilege and proper incident response.

A star schema design organizes data into dimension and fact tables, creating a hub-and-spoke structure that simplifies relationships and enables efficient query performance. By separating descriptive attributes from numeric measures, the model becomes intuitive for business users and allows DAX to filter and aggregate correctly across one-to-many relationships. This is the recommended practice because it balances normalization with usability, reduces model complexity, and ensures that filters propagate predictably from dimensions to facts, which is essential for dynamic reporting.

Implementing incremental refresh on fact tables is the correct solution because it partitions the table by date and only processes partitions that are new or changed since the last refresh, dramatically reducing the amount of data pulled from the source and the storage engine workload. This requires an import-mode dataset with a date-time watermark column, RangeStart and RangeEnd parameters, and proper policy settings for archive periods; it directly targets the root cause of a prolonged refresh cycle by limiting the refresh scope to deltas instead of reprocessing the entire fact table history.

Append Queries is the correct tool because it stacks rows from two or more queries vertically, creating a single output table that contains every record from each input. In Power Query, this operation—equivalent to UNION ALL in SQL—is used when the inputs share a common column schema, such as merging January and February sales records. Appending does not alter existing rows or add columns; it simply lengthens the dataset.

The 'In the Current Year' filter is a dynamic date filter that automatically updates based on the current date. When the report refreshes, it will include only rows where ShipmentDate falls within the current calendar year, satisfying the requirement without manual intervention.

OData (Open Data Protocol) is a native, cloud-friendly REST API standard supported by the OData connector in Power Query Online, so dataflows can easily browse entity sets from APIs like SAP Gateway or Microsoft Graph. Both OData v3 and v4 endpoints are valid, and the connector supports anonymous, Windows, and organizational authentication options.

The M expression, likely using Number.From or a table column type change, requires text to match the current locale's numeric format. Since a comma is not the decimal separator in the default en-US locale, each row containing a comma causes a conversion failure that produces an Error value in the cell. Rather than being corrected or ignored, the transformation faithfully reports the parse failure as an error, which is the expected result.

Disabling load on intermediate queries prevents their result sets from being materialised into the dataset, eliminating unnecessary storage and refresh work. Only the final query's output is loaded, so the refresh engine processes less data and completes faster, directly improving refresh performance.

Creating aggregations on the fact table pre-summarizes data at higher grain levels (e.g., month, category), allowing the Import storage engine to serve queries from a smaller cached table set. Because Power BI's VertiPaq columnar compression and in-memory technology can scan aggregation tables far faster than the full transaction-level fact table, this directly reduces query response time. This is the correct answer because aggregations are a proven, first-class performance feature in Import mode, not merely a side effect of refresh or schema changes.

A scatter chart is the standard visualization for examining the relationship between two numerical variables. It places each observation as a point on an X-Y coordinate system, directly mapping the paired values, and supports the identification of correlation, clustering, outliers, and non-linear patterns. This makes it the only option among those listed that effectively reveals how one variable changes in relation to the other.

On mobile devices, portrait orientation and narrow viewports make multi-column layouts shrink individual visuals below a usable size. A single column lets each visual span the full width of the screen, preserving font size and making interactive elements tappable. It also creates a natural vertical scroll pattern, matching how users expect to browse content on a phone.

In DirectQuery mode, Power BI does not store a local copy of the data; every visual and slicer interaction sends a native query (usually SQL) to the source database. Creating an index on the Region column directly reduces the cost of the WHERE clause filter generated by the slicer, allowing the database engine to perform a seek rather than a full table scan. This is the most effective troubleshooting step because it optimizes the actual bottleneck—source-side predicate evaluation—without changing the semantic model or losing data granularity.

PREVIOUSMONTH is the correct time-intelligence function because it returns a single-column table containing all dates from the calendar month immediately before the last date visible in the current filter context on the 'Date' table. When this table is used as a filter argument inside CALCULATE, it overrides the existing date filtering on the Sales table, so SUM(Sales[Amount]) is evaluated over exactly the prior month's dates. This is the idiomatic DAX pattern for a previous-month measure, and it avoids the shape-preserving ambiguity of PARALLELPERIOD and the forward-looking behavior of NEXTMONTH. No other period-shifting function gives as clean a one-month window as PREVIOUSMONTH in this scenario.

To show only employees who have at least one sales record, a visual-level filter must be applied on the Amount field to exclude blank values (e.g., 'Amount is not blank' or 'Amount > 0'). Without such a filter, the table visual displays every row from the Employee dimension, even those without any related Sales rows, because Power BI's default behavior is to show all dimension rows unless a filter explicitly removes them. A visual-level filter on a measure or column from the fact table is the standard technique to restrict the visual to only employees with sales.

This tenant setting ensures that when data is exported from Power BI to supported formats like PDF, the sensitivity label from the dataset is automatically applied to the exported file. This maintains the protection and compliance requirements. It is the correct configuration to enforce label inheritance for exports, as long as the label is applied to the dataset and the export format supports labeling.

Fact tables should contain numeric, additive measures—such as sales amount, quantity, or margin—and foreign keys that reference dimension tables. This structure is fundamental to star schema design because it enables efficient aggregation and lets facts be filtered by dimension attributes. Without numeric measures or proper foreign keys, the fact table cannot support reliable calculations or relational integrity.

Replace Values is the correct, direct transformation because it performs an in-place, column-wise substitution of nulls with 0 in a single Power Query step. It targets the actual null placeholder (not an error) and is applied to all selected columns simultaneously, making it the most efficient and unambiguous method for this exact requirement.

Ensuring a logical tab order is a core accessibility best practice for keyboard-only and screen reader users. In Power BI, you can define tab order in the Selection pane to match the natural reading flow of the report, so keyboard users can navigate from title to slicers to visuals in a meaningful sequence. This prevents unpredictable jumps and makes the report usable for people with motor impairments who rely on keyboard navigation.

When a Power BI report uses a live connection to Azure Analysis Services (AAS) or SQL Server Analysis Services (SSAS), row-level security defined in the source tabular model is enforced directly by the source. The live connection passes the authenticated user's identity to the server, which then evaluates the RLS roles and filters rows before returning data to Power BI. There is no need for additional configuration in Power BI; the source model is the single authority for data security, ensuring consistent and secure row filtering across all reports that connect live.

TOTALYTD computes a running total across all dates in the current filter context, so when a date slicer is active the measure still accumulates from year start to every date shown, yielding multiple values rather than a single summary. Wrapping it in CALCULATE with LASTDATE('Calendar'[Date]) restricts the filter context to the maximum date in the selected range, forcing the YTD total to evaluate as of that single point. This produces the desired scalar value for a card or summary visual.

A Left Outer Join returns every row from Orders plus matching Returns rows, with nulls where no return exists. This satisfies the constraint of including all orders while attaching only matching returns, since OrderID is the shared key across the SQL Server and Excel sources.

Power BI Performance Analyzer captures per-visual query durations and the exact DAX produced, which you can then paste into DAX Studio for deep profiling. Running the same report as each RLS role (or using DAX Studio's 'Trace as Role'/'User' feature) lets you compare query plans and see how RLS filters are injected, exposing whether they cause excessive storage-engine scans or formula-engine bottlenecks. This combination is the standard way to pinpoint the exact query path responsible for role-specific slowness.

This is the correct approach because you first invite the external partner as a B2B guest user in Microsoft Entra ID, which creates a secure identity in your tenant for them. After they accept the invitation, you can add them to the workspace and assign the Viewer role, restricting them to read-only access. Finally, you share the report or app directly with them, and because the workspace resides in Premium capacity, the guest users can view it without needing their own Power BI Pro licenses. This method ensures authenticated, authorized access while maintaining full security and auditability.

Pre-summarizing data at the source, such as creating group-by views or rollup tables in the underlying database, minimizes the query volume and scan overhead that DirectQuery would otherwise perform on every visual interaction. This allows the database engine to return already aggregated rows, drastically reducing the network payload and CPU usage, especially for large fact tables. While Power BI's own aggregate tables can also help, this option specifically addresses the data-source side of the optimization.

Iterating the Subscriptions table with FILTER and comparing each row's StartDate and EndDate to the date selected in Dates expresses the overlap condition directly, without relying on any active relationship. This is the standard pattern for interval or 'active on date' logic and keeps the physical relationship inactive, avoiding ambiguity.