CCNA Model the data Questions

This option correctly represents a Customer dimension: CustomerID serves as the unique key, while CustomerName, City, and Region are all stable, customer-specific descriptive attributes that are guaranteed to be the same for every order placed by that customer. Each row corresponds to exactly one customer, maintaining the grain and enabling clean one-to-many joins to the fact table. These attributes support meaningful slicing by customer geography without causing duplication or row multiplication in the underlying fact data.

The RLS predicate uses USERPRINCIPALNAME(), which returns the complete user principal name including the domain suffix, for example alice@west.contoso.com. If the Region column stores only simple region values such as 'West' or 'Central', a direct equality comparison will never match because 'West' is not equal to 'alice@west.contoso.com'. Therefore, every row is filtered out and the table appears empty.

Remove Duplicates is the dedicated Power Query transformation that removes rows with identical values across all selected columns, retaining only the first occurrence. By default, it considers all columns, and if a table contains duplicate records, selecting this option from the context menu or the ribbon will directly reduce the row count to only unique combinations. This operation is case-sensitive and treats nulls as values to compare, making it the correct and intended method for eliminating duplicate tabular data.

This is the correct relationship because each customer appears once in the Customers table but can have multiple sales transactions in the Sales table. A one-to-many relationship from Customers to Sales ensures that filtering on Customers propagates to Sales, which is typical for a dimension-to-fact relationship in a star schema.

A measure that uses ALL('Date') or ALLEXCEPT('Date') inside CALCULATE explicitly removes the filter context from the entire Date table or from all Date columns except those specified. For instance, CALCULATE(SUM(Orders[Amount]), ALL('Date')) would ignore a slicer on Date[Year], because ALL('Date') clears every filter applied to the Date table, including the Year column. ALLEXCEPT('Date', 'Date'[Month]) would preserve a Month filter but still ignore Year if Year is not in the exceptions list. This is the classic cause when a measure appears unresponsive to slicer selections, even though the underlying relationships and directions are perfectly normal.

This is the minimum requirement for correct time intelligence in Power BI. A separate date table must include every date in the range spanning the fact table's dates, with no gaps, and be marked as a date table; critically, it must also have a relationship (typically one-to-many) to the fact table's date column so that filters propagate correctly. With this structure, DAX functions such as TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD can safely rely on the continuous calendar to compute period-over-period comparisons. Note that marking the table as a date table is also mandatory, but the separate table and relationship are the foundational prerequisite.

Calculated tables are evaluated in DAX at model load time, making them ideal for constructing a bridge table when two dimension tables have a many-to-many relationship. You can generate the unique key combinations using functions like DISTINCT or SUMMARIZECOLUMNS and store that result physically in the model. This approach resolves ambiguity at the relationship level without requiring changes to your source schema.

Creating a hierarchy in the Date table by placing Year above Quarter above Month defines a parent-child level structure that visuals can traverse. When a report field includes this hierarchy, Power BI automatically adds drill-down and expand controls, letting you move from annual to quarterly to monthly detail in the same chart.

A bridge table is the standard pattern for handling many-to-many relationships in Power BI: it holds unique combinations of the involved keys and connects to the fact table via one-to-many relationships to each dimension. This normalizes the originally ambiguous many-to-many relationship into two clear one-to-many paths, allowing filters from either dimension to propagate correctly without duplicating fact rows. It preserves each dimension's granularity and ensures that measures aggregate exactly once per relevant fact record, making it the correct modeling solution.

Power Query (Get Data) provides native connectors and a rich transformation environment to merge, append, and shape datasets from disparate sources into a single, consistent model. Publishing that model once and configuring a daily scheduled refresh in the Power BI service centralizes maintenance and ensures all visuals receive updated data automatically. This approach aligns with best practices for scalable self-service BI because the refresh burden is handled by the service, not a user.

Importing both tables gives in-memory performance, and a dedicated date table enables time intelligence for year-over-year comparisons. SUMX iterates at row level to compute Quantity * UnitPrice - Discount correctly, satisfying the sales, shipping and YoY requirements.

A dedicated date table enables time-based calculations.

Calculated tables are created using DAX expressions, letting you generate data absent from any source system. A date table built this way provides the continuous calendar required for time intelligence, which source data often lacks, satisfying the need for a complete date dimension.

A star schema's single-hop relationship between each dimension and the fact table means measures written in DAX do not need to traverse multiple relationship levels or perform context transitions across a snowflake chain. Because each filter propagation follows one predictable path, CALCULATE and FILTER functions operate against a well-defined filter context, resulting in shorter, more maintainable measures that are easier to debug and audit. This simplicity directly reduces the amount of DAX needed to create accurate totals and percentage calculations.

The view execution is a fixed prerequisite for the refresh: the Power BI engine must run the source query and wait for the full 5-minute execution to complete before it receives any data to load. Only after the view returns can the data loading phase, including any transformations, indexing, and storage into the in-memory columnstore, take place. Therefore, the absolute minimum refresh duration is 5 minutes, with the actual time being that 5 minutes plus the entire downstream loading and processing time.

Power Query in Power BI Desktop allows importing data from both on-premises SQL Server and cloud-based Salesforce, enabling merging/append operations to shape data into a star schema. This in-memory model supports all relationships and calculations needed.

RELATED is used in a calculated column on the many side of a relationship to fetch a value from the related table on the one side. Since Transactions is related to Accounts via AccountID, RELATED(Accounts[AccountType]) returns the account type for each transaction. This function leverages the existing relationship and is efficient for row-level lookups.

Hiding foreign key columns in dimension tables stops report authors dragging surrogate keys into visuals, which would produce meaningless aggregations. This satisfies the stem's relationship-management constraint by keeping filtering flowing through the defined relationship rather than raw key values, reducing accidental many-to-many ambiguity and incorrect totals.

Filtering CustomerAddress to IsPrimary = True in Power Query before merging into Customer loads only one address per customer, eliminating fan-out and reducing model size. The merge creates a clean dimension table with the primary address attributes, allowing a single active relationship from the fact table to each customer's primary location. This ETL approach minimizes storage and prevents the need for complex DAX or inactive-relationship tricks.

The measure is performing aggregations at the wrong granularity; it should use SUMX to iterate over each row. Using a simple SUM for the revenue calculation multiplies the total sales by the total discount rate, rather than computing the discounted amount for each individual transaction. SUMX creates a row context, evaluates the expression for every row (e.g., Sales[SalesAmount] * (1 - Sales[Discount])), and then sums those intermediate results, ensuring accurate row-level arithmetic before aggregation.

Pre-aggregating in Synapse—for example via CTAS to create a month/account-type grain—shrinks the dataset from tens of billions of rows to a few thousand, so a DirectQuery connection to that aggregate answers all high-level visuals with near-instant response times. The composite model adds the original detail table (imported in a reduced form or also DirectQuery) strictly for drill-through when a user clicks a specific period/account; because drill-through filters to a single account and month first, the detail query touches a modest subset. This is the classic navigation strategy: use aggregated DirectQuery tables for high-cardinality facts, and keep fine-grained details available only when needed, which balances performance, freshness, and interactivity.

SWITCH is the correct choice because it evaluates a single expression against a list of possible values and returns the corresponding result, making it ideal for creating multi-category calculated columns such as 'Low', 'Medium', or 'High'. Unlike nested IF functions, SWITCH keeps the logic linear and readable, and it can evaluate true/false conditions when used with a leading TRUE(), providing a clean row-level classification without context transition complexity.

Removing unnecessary columns from the fact table is correct because DirectQuery operates by pushing queries back to the source, and every extraneous column widens the SELECT statement, increasing network transfer and source-side processing. A narrower fact table means fewer columns are scanned and materialized for each visual interaction, which directly reduces query latency and memory overhead. This is a standard column-pruning practice for DirectQuery performance tuning.

In a one-to-many relationship between Products and Sales, every ProductID in Sales must have a matching ProductID in Products for the related attribute (e.g., Category) to be populated. If Sales contains ProductID values not present in Products, those rows participate in the relationship as unmatched orphans, so the lookup column from Products is blank. As a result, any visual that slices or groups by Category shows a blank bucket that aggregates the sales from those orphaned ProductID values.

Correct. In a composite model, the large fact table typically remains in DirectQuery to avoid memory pressure, but measures that iterate row-by-row (e.g., SUMX, or time-intelligence functions that scan a period) will force the DAX engine to issue multiple or very broad SQL queries against the remote source. Each iteration may expand into a separate query or pull large result sets, causing severe latency and poor report responsiveness. Time intelligence that needs to re-evaluate the fact table for each date context is especially costly and should be avoided in DirectQuery-heavy models.

DATESINPERIOD returns a table of contiguous dates starting from a specified start date and extending for a given number of intervals (months, quarters, or years), either forward or backward. For a rolling 12-month calculation, you can use CALCULATE(SUM(Sales[Amount]), DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)) to include exactly the trailing 12 months up to and including the current maximum date. This function is purpose-built for arbitrary date ranges anchored on a single date, making it the correct choice for this scenario.

This is correct because the dimension table Patients should filter the fact table Visits. A one-to-many relationship from the dimension to the fact table with a single filter direction is the standard star schema design. It ensures that filters on patient demographics propagate to Visits, and it avoids ambiguity and performance issues associated with bidirectional filtering.

Indexing speeds up query execution.

DISTINCTCOUNT(Sales[ProductID]) evaluates the ProductID column within the current filter context and returns the number of unique, non-blank values. It automatically removes duplicates and ignores blanks, providing the exact cardinality of products sold. This is the standard DAX function for 'count of distinct instances' in scenarios like this, and it respects the existing relationship filtering from related tables.

A star schema with a sales fact table at the center and separate date and product dimension tables around it is the optimal relational design for this reporting scenario. The fact table stores quantitative measures (e.g., sales amount, quantity) and foreign keys to each dimension, creating one-to-many relationships that let Power BI filter and group by date or product efficiently. This design avoids data redundancy, supports correct grain granularity, and ensures the model can answer time-based and product-based questions independently or together. It also aligns with Power BI's VertiPaq columnar storage, which compresses dimensions well and speeds up aggregation.

Integer keys improve join performance.

Static row-level security is implemented by creating one or more roles whose DAX filters use constant values, such as [Country] = "USA". Every user assigned to a specific role receives exactly the same filtered view; to grant different views, you must create separate roles for each distinct filter value. This approach is predictable and easy to audit, but it becomes cumbersome when many user groups need different subsets of rows.

Order Details is the correct fact table because each row represents a line item from an order, the lowest grain of the sales process, and it holds numeric measures like quantity, unit price, and discount that can be aggregated with SUM and similar functions. It also contains foreign key columns (OrderID, ProductID, CustomerID, etc.) that link to the surrounding dimension tables, creating the star schema. The presence of both additive measures and multiple dimension keys is the defining property of a fact table.

DATESINPERIOD is a general-purpose time intelligence function that returns a table of dates from an initial start date and moves a specified number of intervals (such as -12 months) into the past or future. When used with a start date of the current context date and an interval of -12 MONTH, it dynamically constructs the exact trailing 12-month period, including all dates from the current date going back one year. This makes it the correct choice for a last-12-months measure because it directly defines the rolling window based on the current filter context.

DATESYTD requires a gapless, contiguous set of dates spanning the entire year because it internally constructs a date range from January 1 through the last visible date in the filter context. If the Date table has missing dates—for example, only trading days or a subset of dates—the year-to-date calculation cannot include those gaps, causing blanks or understated totals. A properly built continuous date table with every calendar date is mandatory for time intelligence functions.

A defining characteristic of DirectQuery is that report interactions—such as filtering, slicing, or simply changing a page—are translated in real time into native queries executed directly against the underlying data source. This ensures that the report always shows the current state of the source, but it also means query performance depends heavily on the source's responsiveness and indexing. Since no snapshot is taken, any change in the source is immediately reflected in the report.

In DirectQuery mode, Power BI sends every visual query directly to the underlying SQL Server, so query performance depends on the source engine's ability to return results quickly. Creating indexes on columns used in filters and relationship joins lets SQL Server use efficient lookup and merge operations instead of full table scans, dramatically reducing query latency. Without appropriate indexes, even simple filter operations can force the database to scan millions of rows, which directly degrades the Power BI report experience.

Creating a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID] is the correct star schema design. This relationship lets filter context flow from the dimension table (Customers) to the fact table (Sales), enabling correct aggregations like total sales by customer name. It also uses Power BI's optimized VertiPaq engine to join tables only when needed, reducing memory and improving query performance compared with calculated columns or merged tables. The cardinality is correctly specified because one customer can appear in many sales records.

Roles in Power BI define row-level security by using DAX expressions as filter predicates. For example, a role can be configured with a DAX filter like `[SalesRegion] = "North"` or a more complex FILTER expression that returns a table of allowed rows. These expressions are evaluated within the filter context of each row, and only rows that return TRUE for the predicate become visible to users assigned to that role. This makes DAX the core mechanism for implementing custom, flexible row-level security in Power BI.

Marking a table as a date table in Power BI Desktop (via Table tools > Mark as date table) establishes it as the model's official calendar source. This designation is required for time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATESBETWEEN to operate correctly. Power BI validates that the chosen date column contains continuous, unique dates and automatically uses it for date-based calculations and hierarchy generation.

Creating a calculated NetAmount column in Power Query (M) evaluates Quantity * UnitPrice - Discount once at refresh time, storing the result as a static column in the data model. The subsequent measure SUM(Sales[NetAmount]) simply aggregates those pre-computed values, avoiding row-by-row evaluation at report time and improving query performance for large fact tables. Because the calculation is pushed to the query engine instead of the DAX engine, it also keeps the code simpler and avoids iterator overhead during visual rendering.

CustomerID, ProductID, and OrderDate are all foreign keys that establish relationships to the Customer, Product, and Date dimension tables. In a proper star schema, these keys should be replaced with dimension attributes such as CustomerName, ProductCategory, and OrderMonth during reporting. This replacement makes reports readable and lets users filter and group by meaningful descriptions instead of opaque ID numbers.

SUM is the correct DAX function to add all values in a numeric column, such as Sales[SalesAmount]. It aggregates the column across the filter context, providing the total sales amount. This is a fundamental aggregation function for measures and is efficient because it operates on a single column without iterating rows.

TOTALYTD is a DAX time intelligence function that evaluates an expression over the year-to-date period based on a given date column. It returns a scalar value representing the cumulative total from the start of the year to the latest date in the current filter context. This function is specifically designed for time-based calculations, making it a valid answer.

The many-to-one (*:1) relationship from Orders to Customers is correct because the Orders table contains many rows that reference the same single customer row through the customer key. Each order belongs to exactly one customer, so the CustomerID column is not unique in Orders but is unique in Customers. This is the standard fact-to-dimension relationship in Power BI, where filtering from Customers to Orders yields all orders for that customer.

This is a correct use case for composite models. In Power BI, a composite model allows you to combine tables from different storage modes in a single data model. A DirectQuery table can be configured to always pull the latest data from the source, while historical tables can be imported and refreshed on a schedule. This hybrid approach gives you real-time visibility into current transactions without giving up the performance benefits of pre-loaded historical data.

Power BI's auto-date/time feature silently creates hidden date tables for every date column, inflating model size and creating extra relationships that can confuse the report layer. Disabling this option forces you to use an explicit date table, giving you control over data type, granularity, and contiguous date ranges, which improves time-intelligence performance and reduces memory footprint.

This is the canonical star-schema relationship. A single customer can appear in many orders, so Customers is the 'one' side and Orders is the 'many' side. By placing the relationship from Customers to Orders, customer attributes (region, segment, etc.) automatically filter and slice all related order rows, enabling correct aggregations in measures. This relationship uses the dimension table as the lookup table and the fact table as the data table, which is the preferred pattern for performance and intuitive filtering.

DATEDIFF(Orders[OrderDate], Orders[ShipDate], DAY) is correct because the DATEDIFF function in DAX returns the number of interval boundaries crossed between a start date and an end date, with the third argument specifying the interval unit. Here, DAY asks for whole calendar days, so the result is an integer representing the total days from OrderDate to ShipDate. This is the intended calculation for order-to-ship time.

DATESINPERIOD correctly constructs a contiguous range of dates ending at MAX(Date[Date]) and extending backward 12 months using the MONTH interval. This creates a filter context containing approximately 365 days that includes the current month and the preceding 11 months, so the CALCULATE SUM aggregates all sales attributable to that trailing twelve-month window. Because the end date is dynamically determined by the active filter context and the interval is relative, the measure automatically updates as the user selects different time periods, making it the accurate rolling 12-month total.

A calculated table in DAX is materialized in memory when the model is refreshed, meaning its rows and values are stored as static data in the VertiPaq engine. Slicer selections generate a filter context at query time and can only filter visuals over existing rows; they cannot re-execute the table expression for each selection. To make a summary respond to slicers, the aggregation must be defined as a measure or use a dynamic technique such as a disconnected table with measures.

Importing all tables into memory and applying an incremental refresh policy to the Sales table to keep only the last 5 years is the correct approach because it reduces the 500M-row table to a manageable subset that leverages the VertiPaq columnstore engine for in-memory aggregations. Incremental refresh uses RangeStart/RangeEnd parameters to filter historical data during each refresh, while keeping the model's date table and time-intelligence functions intact. This delivers fast, consistent performance and satisfies the requirement for a date hierarchy without sacrificing freshness of the most recent data.