Practice Test #5

At the year level, the filter context includes all dates in that year. LASTDATE('Date'[Date]) returns only the final date of that year (e.g., 31-Dec for calendar year, or the last day of the financial year if the hierarchy uses financial year boundaries). CALCULATE then evaluates DISTINCTCOUNT(Balances[AccountID]) only for that single last date. So the visual will show the number of accounts that were live on the last day of the year, not the number of accounts that were live throughout the year. “Live throughout the year” would require accounts to have Balances rows for every day in the year (or to meet a start/end date condition spanning the whole year), which this measure does not test. Therefore, the statement is false.

At the month level, the filter context includes all days in the selected month. LASTDATE returns the last day of that month in the current context. The measure then counts distinct accounts present in Balances on that last day only. That result is not “the total number of accounts that were live throughout the month.” It is “accounts live on the last day of the month.” Accounts that were active earlier in the month but closed before month-end will be excluded, and accounts opened mid-month but still active at month-end will be included (even though they were not live for the full month). To compute “throughout the month,” you’d need logic ensuring the account appears on every day of the month (or spans the full month). Hence, No.

At the day level, the filter context is already a single date (each row in the table visual corresponds to one day). In that context, LASTDATE('Date'[Date]) returns that same date. Therefore, CALCULATE applies a filter that effectively keeps the same single day. Because Balances contains a daily record for every account that is live on that day, DISTINCTCOUNT(Balances[AccountID]) for that date returns the number of accounts live that day. This matches the statement exactly. Therefore, Yes.

Correct answer: B (Cross filter direction). In an imported Power BI model, setting relationships to single direction (from the 1-side dimension tables to the *-side fact/transaction table) typically improves query performance and reduces ambiguity. Bidirectional (Both) cross-filtering can cause more complex filter propagation, larger intermediate result sets, and can introduce ambiguous paths that force the engine to do extra work or require additional logic in DAX. In the exhibit, several relationships appear to use bidirectional filtering (the icon in the relationship line), which is a common cause of slower visuals. Why not A (Cardinality): Cardinality should reflect the true data relationship (1:* vs *:*). Changing it incorrectly can break correctness; it’s not a general performance “tuning knob.” Why not C (Assume Referential Integrity): This setting is primarily relevant for DirectQuery (enables INNER JOIN optimizations). The dataset is explicitly imported, so it won’t provide the same performance benefit here.

Correct answer: A (star schema). A star schema consists of a central fact table connected directly to multiple dimension tables, with 1:* relationships from each dimension to the fact. In the diagram, Employee is the central table and it connects directly to Date, BU, PayType, Gender, AgeGroup, Ethnicity, SeparationReason, and FP. Those surrounding tables look like classic dimensions (descriptive attributes used for slicing/filtering), while Employee contains the keys and measures/attributes used for analysis. Why not B (snowflake schema): A snowflake schema would show normalized dimensions where a dimension links to other dimensions (for example, a Geography dimension linking to Country/Region tables). The exhibit does not show dimension-to-dimension chains; most tables connect directly to Employee. Why not C (denormalized table): A denormalized model would typically have fewer tables (often one wide table) with repeated descriptive columns rather than separate lookup/dimension tables.

No. In the scenario description, View1 explicitly lists Class ID as data type Int (not Bigint). The question statement for this sub-item says “Class ID is of data type Bigint,” which contradicts the provided table for View1. Why this matters for modeling: in Power BI, relationship columns should have matching data types between tables. If Class ID were Bigint in one view and Int in another, you’d need to cast/convert in Power Query or in the SQL view to avoid relationship issues and potential performance problems. But based on the given metadata, Class ID is Int, so the statement is false.

No. The scenario does not provide the data type for Class Name, so you cannot conclude that it is VARCHAR(200). The only data types explicitly shown are for the columns listed from View1, and View2's detailed schema is not provided here. Therefore, the statement should be marked No because it is not supported by the given information.

No. The scenario does not specify the data type of Class Subject. Although VARCHAR(100) may be plausible in a real system, PL-300 questions must be answered from the information provided, and that information is absent here. Because the schema details for View2 are not shown, the statement cannot be validated and should be answered No.

No. The scenario does not explicitly show Teacher ID or its data type. While Int is a common choice for identifier columns, the exam item must be answered based on stated metadata rather than assumptions. Since View2's schema is not fully visible, the statement is unsupported and should be marked No.

No. The scenario does not provide the data type for Teacher First Name. Even though VARCHAR(100) would be reasonable for a name column, the question does not include that metadata. Because the type is not explicitly given, the correct response is No.

No. The data type for Teacher Last Name is not shown in the scenario. A text type such as VARCHAR would be typical, but the exact length cannot be inferred from the provided information. Since the statement is not supported by the visible schema, it should be answered No.

Yes. Period Number is explicitly shown in View1 as Tinyint. Period numbers are small integers (e.g., 1–12), so Tinyint is an appropriate SQL type. For modeling, Period Number is a common grouping attribute for visuals like “count of classes by period” and “attendance by period by day.” Even though it appears in the attendance view, in a star schema you generally want period-related descriptors to be in a dimension (Class or Period) to support consistent slicing and potentially add attributes like start/end time.

No. The scenario does not specify the data type for School Year. Although school year is often stored as text, it could also be represented in other ways, and the exact type is not given here. Because the metadata is missing, the statement should be marked No.

No. The scenario does not show the data type for Period Start Time. Time would be a sensible design choice, but the question provides no explicit schema details for that column. Since the type cannot be verified from the prompt, the correct answer is No.

No. The scenario does not provide the data type for Period End Time. Although a SQL Time type would be appropriate in practice, the exam item must be answered from the stated metadata only. Because that metadata is not shown, the statement should be answered No.

Teacher First Name should be in the Teacher dimension. It is a descriptive attribute (non-additive) that describes the teacher entity, not an event. Storing it in a fact table would repeat the same text for every attendance record and harm model size, compression, and performance. Why not the other options: - Attendance fact: would denormalize descriptive text into the fact and violate star schema best practice. - Class dimension: while a class is associated with a teacher, teacher name is not an attribute of the class entity itself; it’s an attribute of the teacher entity. Keeping a separate Teacher dimension supports reuse (a teacher teaches many classes) and cleaner relationships. - Teacher fact: “Teacher fact” is not a typical concept here; facts represent measurable events, not master data like names.

Period Number should be in the Class dimension (given the available choices). The prompt states that Class ID uniquely identifies the specified class, period, teacher, and school year. That means Period Number is functionally determined by Class ID and is a descriptive attribute of that class offering/schedule. Putting Period Number in the Class dimension supports “count of classes by period” by counting Class IDs grouped by Period Number. Why not the other options: - Attendance fact: although Period Number exists in View1, keeping it only in the fact can lead to duplicated attributes and makes it harder to attach additional period descriptors (start/end time) without repeating them. - Teacher dimension: period is not a teacher attribute. - Teacher fact: not applicable; period is not a measure/event about teachers.