PL-300 Model the data Practice Test 9 — 15 Questions

Question 1

You need to create a relationship between two tables where one table has duplicate values on the key column. Which cardinality should you choose?

Accepted Answer

Many-to-one. Many-to-one, because the scenario specifies that the table on the key side contains duplicate values, meaning many rows on that side relate to a single row on the other side. In a many-to-one relationship, the 'many' side is the table with duplicate foreign key values, and the 'one' side is the table with unique key values, which matches the described data. One-to-one (A) requires unique values on both sides, so it cannot accommodate duplicates. Many-to-many (B) requires a junction table and is not the direct cardinality between the two tables described. One-to-many (D) describes the same relationship from the opposite direction, but the question asks for the cardinality to choose given duplicates on the key column, which is many-to-one.

Answer

One-to-one

Answer

Many-to-many

Answer

One-to-many

Question 2

You are designing a Power BI data model for a manufacturing company. Which TWO practices help optimize performance when using DirectQuery?

Accepted Answer

Reduce the number of columns in the fact query to only those needed. Reducing the number of columns in the fact query minimizes data transfer and improves DirectQuery performance. Option E is correct: Using a single date dimension table for all date columns simplifies relationships and reduces the number of joins required, enhancing query efficiency. Option A is wrong because disabling relationships would break the model integrity and force manual filtering, degrading performance. Option B is wrong because calculated columns in DirectQuery are pushed to the source, but they can still increase query complexity and resource usage; it is better to use native columns or measures. Option D is wrong because bidirectional cross-filtering can lead to ambiguous relationships and additional query overhead, harming performance.

Answer

Disable relationships between tables to reduce query complexity

Answer

Create calculated columns in Power Query instead of in DAX

Answer

Enable bidirectional cross-filtering for all relationships

Question 3

You are modeling data with a many-to-many relationship between 'Student' and 'Class' tables. Which THREE configurations can handle this scenario?

Accepted Answer

Use CROSSFILTER function to enable many-to-many filtering. Option A is correct because the CROSSFILTER function in DAX can modify the filter direction of an existing relationship (e.g., CROSSFILTER(Student[StudentID], Class[ClassID], BOTH)) to allow bidirectional filtering that effectively handles many-to-many scenarios without a bridge table. Option C is correct because creating a bridge (junction) table containing StudentID and ClassID is the canonical star-schema solution: two one-to-many relationships from the bridge to Student and Class resolve the many-to-many relationship cleanly and support proper filter propagation. Option E is correct because a calculated column that concatenates Class names in the Student table denormalizes the relationship into a single table, letting you report on the combined values without needing a physical many-to-many relationship. Option B is not correct because removing duplicate rows does not resolve a many-to-many cardinality issue and would destroy legitimate enrollment data. Option D is not correct because creating a unique key to force a one-to-many relationship does not model the many-to-many reality and would misrepresent the data.

Answer

Remove duplicate rows from both tables

Answer

Change relationship to one-to-many by creating a unique key

Question 4

You are a Power BI developer for a healthcare organization. You are building a semantic model to analyze patient readmission rates. The data warehouse contains: Admissions (AdmissionID, PatientID, AdmitDate, DischargeDate, DiagnosisCode, HospitalID) and Readmissions (ReadmissionID, PatientID, ReadmitDate, Reason). You need to create a measure that calculates the readmission rate within 30 days of discharge. The measure must consider only the first readmission per patient per admission. You also need to create a visual that shows the trend of readmission rates over time, allowing users to filter by diagnosis. The model will be used by 200 analysts and must refresh daily. Which DAX measure should you create?

Accepted Answer

Readmission Rate = DIVIDE( COUNT(Readmissions[ReadmissionID]), COUNT(Admissions[AdmissionID]) ). None of the listed options satisfies the stated requirements. Option D only computes a simple readmission-to-admission ratio and does not enforce the 30-day window or the first-readmission-per-admission rule; it should not be marked correct. Option C attempts the window but contains an invalid DAX row-context reference. The question requires a corrected DAX measure or corrected choices.

Answer

Readmission Rate = DIVIDE( COUNTROWS(Readmissions), COUNTROWS(Admissions) )

Answer

Readmission Rate = DIVIDE( COUNTROWS(Admissions), DISTINCTCOUNT(Admissions[PatientID]) )

Answer

Readmission Rate = VAR ReadmitPatients = CALCULATETABLE( VALUES(Readmissions[PatientID]), Readmissions[ReadmitDate] <= Admissions[DischargeDate] + 30 ) RETURN DIVIDE( COUNTROWS(ReadmitPatients), DISTINCTCOUNT(Admissions[AdmissionID]) )

Question 5

You are a data analyst for a university. You need to build a Power BI report to analyze student enrollment by program and year. Data sources: a CSV file with student enrollment records (StudentID, Program, EnrollmentYear, Status) and an Excel file with program details (ProgramCode, ProgramName, Faculty). The CSV file contains 1 million rows. The report must allow users to filter by faculty and program, and see a bar chart of enrollment counts by year. You need to design the data model. What should you do?

Accepted Answer

Import both tables into Power BI, create a relationship between Enrollment[Program] and Programs[ProgramCode], use Star schema design.. Option D is correct because importing both tables and building a relationship between Enrollment[Program] and Programs[ProgramCode] creates a proper star schema, where the smaller Programs table acts as a dimension and the 1-million-row Enrollment table as a fact table, enabling efficient filtering by faculty and program and correct aggregation for the enrollment-by-year bar chart. Import mode is appropriate here since the data volume (1M rows) is well within Power BI's capacity and delivers the best query performance. Option A is wrong because mixing Import and DirectQuery adds complexity and latency without benefit, and DirectQuery on a small Excel file is unnecessary. Option B is wrong because without relationships, filters cannot propagate across tables, so faculty/program filtering would not work correctly. Option C is wrong because merging into one wide table denormalizes the model, increases storage and refresh cost, and loses the clean dimension/fact separation that supports flexible slicing.

Answer

Import the CSV file only and use DirectQuery for the Excel file to save memory.

Answer

Import both tables but do not create relationships; use visual-level filters to synchronize selections.

Answer

Merge the two tables in Power Query into a single table to simplify the model.

Question 6

You have a Power BI data model with a table named 'Sales' that has a many-to-many relationship with 'Product' through a bridge table. When creating a measure to calculate total sales by product, you notice the totals are double-counting. What is the most likely cause?

Accepted Answer

The cross-filter direction is set to single instead of both. The cross-filter direction is set to single instead of both. In a many-to-many relationship implemented through a bridge table, the bridge must propagate filters in both directions so that filters on Product flow through the bridge to Sales and vice versa; if the cross-filter direction is single, the filter path is incomplete and the measure can aggregate rows incorrectly, producing double-counted totals. Options A, B, and C do not fit this scenario: a missing date dimension is irrelevant to product-level sales aggregation, incorrect data types in Product would cause type or relationship errors rather than double-counting, and duplicate rows in the bridge table would inflate counts but are not the specific cause described by the cross-filter direction setting.

Answer

The model lacks a date dimension

Answer

The Product table has incorrect data types

Answer

The bridge table has duplicate rows

Question 7

You have a Power BI data model with a table 'Orders' related to 'Customers' (one-to-many). A measure calculates total sales per customer. Some customers have no orders, but they still appear in the report with blank sales. What should you do to hide these customers?

Accepted Answer

Remove customers with no orders from the Customers table using Power Query. The correct option is A: Remove customers with no orders from the Customers table using Power Query. In a one-to-many relationship, the Customers table is the 'one' side, so every customer row appears in the report regardless of whether matching Orders exist, producing blank sales; filtering those rows out in Power Query (e.g., an anti-join or removing rows with null order keys) eliminates them from the model entirely. Option B (bidirectional cross-filtering) only changes filter propagation and does not remove customers with no orders. Option C (a visual-level filter excluding blanks) hides them only in that one visual, not across the model, and can behave inconsistently. Option D (marking a date table) is unrelated to customer filtering and affects time intelligence only.

Answer

Change the relationship cross-filter direction to both

Answer

Add a visual-level filter to exclude blanks

Answer

Mark the date table as a date table

Question 8

You are creating a calculated table in Power BI that contains a list of unique products from the 'Sales' table. Which DAX function should you use?

Accepted Answer

DISTINCT(Sales[Product]). The DISTINCT function returns a single-column table of unique values from the specified column, removing duplicates. It is preferred over VALUES in this scenario because, unlike VALUES, it does not add a blank row when there are referential integrity gaps, ensuring a cleaner product list.

Answer

SUMMARIZE(Sales, Sales[Product])

Answer

VALUES(Sales[Product])

Answer

ALL(Sales[Product])

Question 9

You have a Power BI report that shows sales by region. The data model contains a 'Sales' fact table and a 'Region' dimension table. When you filter by a specific region, the total sales for that region is correct, but the grand total shows all sales. What is the likely cause?

Accepted Answer

The relationship between Sales and Region is inactive. The relationship between Sales and Region is inactive. When a relationship is inactive, the filter applied to the Region column does not propagate to the Sales table, so the visual shows the correct value for the filtered region only if another active path exists, while the grand total still aggregates all rows from Sales. An inactive relationship must be activated with USERELATIONSHIP in a CALCULATE expression or made active in the model for the filter to flow. Option A is not the cause because single cross-filter direction still propagates filters from the one-side Region table to the many-side Sales table. Option B is irrelevant because SUM versus SUMX affects row context and iteration, not whether a dimension filter reaches the fact table. Option C is not a real Power BI setting that would cause this behavior.

Answer

The relationship is set to single cross-filter direction

Answer

The measure is using SUM instead of SUMX

Answer

The Region table is not marked as a dimension table

Question 10

You want to create a measure that calculates the total sales for the current year. Which DAX expression should you use?

Accepted Answer

TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]). TOTALYTD(SUM(Sales[Amount]), 'Date'[Date]) is the correct choice because TOTALYTD is a DAX time-intelligence function that evaluates an expression (here SUM(Sales[Amount])) over the year-to-date period ending at the last date in the specified Date column, directly producing the current year's cumulative total sales. It handles the year boundary automatically via the Date table's marked date column, so no manual filtering is needed. DATESYTD only returns a table of dates and cannot by itself compute a sales total, so it must be wrapped in CALCULATE. CALCULATE(SUM(Sales[Amount]), YEAR('Date'[Date]) = YEAR(TODAY())) is not valid time intelligence because YEAR() returns a scalar, not a filter table, and it ignores the report's date context. SAMEPERIODLASTYEAR is a time-intelligence function that shifts the period back one year and also returns a table, so it neither sums sales nor targets the current year.

Answer

DATESYTD('Date'[Date])

Answer

CALCULATE(SUM(Sales[Amount]), YEAR('Date'[Date]) = YEAR(TODAY()))

Answer

SAMEPERIODLASTYEAR(SUM(Sales[Amount]), 'Date'[Date])

Question 11

Which THREE of the following are valid reasons to create a calculated table in Power BI?

Accepted Answer

Create a summary table that aggregates data from another table. Option B is correct because calculated tables are commonly used to build summary or aggregate tables (for example, using SUMMARIZE or GROUPBY) that consolidate data from existing tables for reporting and model efficiency. Option D is correct because calculated tables can produce a crossjoin of two dimension tables (for example, using CROSSJOIN or GENERATE), which is useful for scenarios like creating a bridge or a many-to-many relationship helper table. Option E is correct because calculated tables are the standard way to generate a date table in DAX (for example, using CALENDAR or CALENDARAUTO) when no suitable date table exists in the source data. Option A is not valid because incremental refresh is configured on the dataset/table refresh policy, not replaced by a calculated table. Option C is not valid because modifying source data before loading is the role of Power Query transformations, not calculated tables, which are computed after data is loaded into the model.

Answer

Replace incremental refresh

Answer

Modify the source data before loading

Question 12

Which TWO of the following are valid DAX functions for filtering data?

Accepted Answer

FILTER. FILTER (B) is a valid DAX function for filtering data because it returns a table that is a subset of another table or expression, evaluating a boolean condition row by row and is commonly used inside CALCULATE to apply row-level filter context. ALL (D) is also a valid DAX filtering function because it removes filters from a table or column (or returns all rows/values ignoring existing filter context), which is essential for overriding or clearing filters in calculations. CALCULATE (A) modifies filter context but is a context-transition/evaluation function rather than a dedicated filtering function in the sense tested here, and it is not marked correct. SUM (C) is a pure aggregation function that adds numeric values and does not filter data. DISTINCT (E) returns a one-column table of unique values and, while it can feed into filters, is not itself a filtering function.

Answer

CALCULATE

Answer

SUM

Answer

DISTINCT

Question 13

You are a Power BI developer at a retail company. You have a data model with a 'Sales' fact table (10 million rows) and dimension tables: 'Date', 'Customer', 'Product', 'Store'. The 'Sales' table includes columns: SalesID, DateKey, CustomerID, ProductID, StoreID, Quantity, UnitPrice, Discount, TotalAmount. The 'Product' dimension has 5,000 rows and includes columns: ProductID, ProductName, Category, SubCategory, Brand, Price. The 'Store' dimension has 200 rows and includes columns: StoreID, StoreName, Region, City, Manager. The 'Customer' dimension has 100,000 rows. The report currently has a measure 'Total Sales' = SUM(Sales[TotalAmount]) and a measure 'Total Quantity' = SUM(Sales[Quantity]). Users complain that the report is slow when filtering by multiple categories and regions simultaneously. You need to improve performance without changing the data source. Which action should you take first?

Accepted Answer

Reduce the number of columns in the Customer dimension by removing unused columns. The [CORRECT] answer is D because the Customer dimension has 100,000 rows and likely contains many unused columns that inflate the model size and slow down query processing. Removing unused columns reduces the memory footprint and improves compression, leading to faster filter propagation and overall report performance. This is a low-risk, high-impact optimization that directly addresses the symptom of slow filtering across multiple dimensions. Since the data source cannot be changed, this model-level change is the most immediate and effective first step.

Answer

Create an aggregation table for the Sales table

Answer

Disable the auto date/time feature in Power BI

Answer

Change the storage mode of the Sales table to Dual

Question 14

You are building a Power BI report for a logistics company. The data source is a SQL Server database with tables: 'Shipments' (columns: ShipmentID, OrderDate, ShipDate, CustomerID, Origin, Destination, Weight, Cost, Revenue), 'Customers' (CustomerID, CustomerName, Segment, Country), and 'Calendar' (Date, Year, Month, Quarter). You need to create a star schema in Power BI. You have already created the relationships. When you try to create a measure to calculate the average delivery time (ShipDate - OrderDate), you get an error because the data types are datetime. You want to create a calculated column in the Shipments table that shows the delivery time in days. Which DAX expression should you use?

Accepted Answer

DeliveryDays = DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY). Option D is correct because DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY) returns an integer number of days between the two datetime columns, which is exactly what a calculated column for delivery time in days requires. In DAX, subtracting two datetime values (as in option A) yields a decimal duration in days, not a clean integer day count, and it can also produce unexpected fractional results. Option B is wrong because CALCULATE is a filter-context function and does not change the row-by-row evaluation needed here, and DATEDIFF does not require CALCULATE. Option C is wrong because FORMAT returns a text string, not a numeric value, so it cannot be used for numeric aggregation or sorting.

Answer

DeliveryDays = Shipments[ShipDate] - Shipments[OrderDate]

Answer

DeliveryDays = CALCULATE(DATEDIFF(Shipments[OrderDate], Shipments[ShipDate], DAY))

Answer

DeliveryDays = FORMAT(Shipments[ShipDate] - Shipments[OrderDate], "d")

Question 15

You are a Power BI analyst at a marketing agency. You have a data model with a 'Campaigns' table (columns: CampaignID, CampaignName, StartDate, EndDate, Budget, Spent) and a 'Results' table (columns: ResultID, CampaignID, Date, Impressions, Clicks, Conversions). You need to model the data to analyze campaign performance over time. You create a relationship between Campaigns and Results on CampaignID (one-to-many). You also create a date dimension 'Calendar' and relate it to Results[Date]. However, when you create a measure to calculate the total budget by month, the budget for a campaign that spans multiple months is counted in each month, leading to double-counting. What should you do to accurately allocate the budget across months?

Accepted Answer

Create a calculated table using CROSSJOIN between Campaigns and Calendar, then use it as a bridge. The correct option is A: create a calculated table using CROSSJOIN between Campaigns and Calendar and use it as a bridge. Because Campaigns relates to Results on CampaignID and Results relates to Calendar on Date, a campaign's single Budget value is repeated for every month that has Results rows, causing double-counting; a bridge table that expands each campaign across the months it spans (e.g., via CROSSJOIN filtered to StartDate–EndDate) lets you allocate Budget per campaign-month and relate it correctly to Calendar. Option B does not help because bidirectional cross-filtering on CampaignID cannot split a single Budget value across months. Option C is irrelevant because USERELATIONSHIP only activates an existing inactive relationship, and no inactive relationship is described. Option D is wrong because dividing Budget by the number of months in a calculated column still leaves the value repeated across months and does not create the proper month-level grain.

Answer

Change the relationship between Campaigns and Results to bidirectional

Answer

Use the DAX function USERELATIONSHIP to activate an inactive relationship

Answer

Add a calculated column to Campaigns that divides the budget by the number of months