PL-300 Exam Questions Part 5 | Advanced Modeling & RLS (Q101-125)

Question 101:

HOTSPOT – You have a Power BI report named Orders that supports analysis of total sales, order counts, and new/repeat customers. The data model size is nearing its limit. The model view and data view for the dataset are shown in the exhibits. For each of the following statements, select Yes if the statement is true, Otherwise, select No.

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Summarizing the Orders table by CustomerID, OrderID, and OrderDate will reduce the number of rows.
• Removing the OrderID column will significantly reduce the model size.
• Replacing the OrderDate column with a MonthID integer column will reduce the model size.

Show Answer

Answer:
– Summarizing the Orders table…: No. Since OrderID is unique for each row, grouping by it (along with other columns) will not reduce the row count.
– Removing the OrderID column…: Yes. OrderID is a high-cardinality column (many unique values). Removing it would allow for better compression and significantly reduce model size.
– Replacing the OrderDate column…: Yes. A DateTime column has very high cardinality. Replacing it with an integer key (e.g., 202301) that links to a date table drastically reduces cardinality and model size.

Question 102:

DRAG DROP – You receive revenue data from an Excel source that needs to be cleaned in Power Query, as shown in the exhibit. You need to transform the data to enable visuals that show revenue by year and product. The solution must ensure columns are named appropriately. Which three actions should you perform in sequence?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

Show Answer

Answer: The correct sequence of actions is:

1. Select Use First Row as Headers to promote the product names to column headers.
2. Select the Department and Product columns, and then select Unpivot Other Columns. This will transform the year columns into two new columns: ‘Attribute’ (containing the year) and ‘Value’ (containing the revenue).
3. Rename the ‘Attribute’ column to YEAR and the ‘Value’ column to REVENUE for clarity.

Question 103:

Note: This question is part of a series… You have a Power BI report with a sales table that has three date keys (Due Date, Order Date, Delivery Date). You need to support analysis of sales over time based on all three dates.
Solution: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and Delivery Date.
Does this meet the goal?

• A. Yes
• B. No

Show Answer

Answer: A
Yes. Referencing a query in Power Query is an efficient way to create multiple, independent tables (role-playing dimensions) from a single source query. This allows you to create three separate date tables in the model, each with an active relationship to a different date key in the sales table, meeting the requirement without re-importing the source data three times.

Question 104:

Note: This question is part of a series… You have a Power BI report with a sales table that has three date keys. You need to support analysis of sales over time based on all three dates.
Solution: For each date foreign key, you add inactive relationships between the sales table and the date table.
Does this meet the goal?

• A. Yes
• B. No

Show Answer

Answer: B
No. Simply adding inactive relationships does not, by itself, enable the analysis. This action is incomplete. To make this solution work, you would also need to create separate measures that use the `USERELATIONSHIP` function to activate the correct inactive relationship for each type of analysis (e.g., a measure for Sales by Due Date, another for Sales by Order Date).

Question 105:

Note: This question is part of a series… You are modeling data from a large SQL Server table named Order with over 100 million records. During development, you need to import a sample of the data from the Order table.
Solution: You add a report-level filter that filters based on the order date.
Does this meet the goal?

• A. Yes
• B. No

Show Answer

Answer: B
No. A report-level filter only filters the data *after* it has already been imported into the Power BI model. This solution would still attempt to import all 100 million records, failing to meet the goal of importing only a sample during development.

Question 106:

You import a dataset with a Product Inventory table containing 25 million rows. The table has columns: DateKey, ProductKey, MovementDate, and UnitCost. You need to reduce the size of the data model without losing information. What should you do?

• A. Change Summarization for DateKey to Don’t Summarize.
• B. Remove the relationship between Date and Product Inventory.
• C. Change the data type of UnitCost to Integer.
• D. Remove MovementDate.

Show Answer

Answer: D
The Product Inventory table already has a DateKey column to relate to the Date table. The MovementDate column is redundant if all date-based analysis can be performed using the Date table. Removing this high-cardinality datetime column will significantly reduce the model size without losing analytical capability.

Question 107:

HOTSPOT – You are creating a data model with Products and ProductCategory tables. Each product has one product category. You need to ensure that you can analyze sales by product category. How should you configure the relationship from ProductCategory to Products?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Cardinality: [Option]
• Cross filter direction: [Option]

Show Answer

Answer:
– Cardinality: One-to-many (One category can have many products).
– Cross filter direction: Single (Filters should flow from the ‘one’ side, ProductCategory, to the ‘many’ side, Products, for best performance and to avoid ambiguity).

Question 108:

You have tables for Impressions (30 million records/month), Ads, and Campaigns. You need to create an ad analytics system to present impression counts by day, campaign, and site_name for the last year, while minimizing the data model size. Which two actions should you perform?

• A. Create one-to-many relationships between the tables.
• B. Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.
• C. Create a calculated table that contains Ad_id, Site_name, and Impression_date.
• D. Create a calculated measure that aggregates by using the COUNTROWS function.

Show Answer

Answer: A, B
B: The most effective way to minimize model size with such a large fact table is to pre-aggregate it in Power Query. Grouping by the required dimensions (Ad_id, Site_name, Impression_date) and counting the rows drastically reduces the number of records loaded into the model. A: After creating the aggregated fact table and dimension tables, you must establish standard one-to-many relationships to build the star schema.

Question 109:

HOTSPOT – You have a Power BI report with the following DAX measure:
Accounts := CALCULATE ( DISTINCTCOUNT (Balances[AccountID]), LASTDATE ('Date'[Date]) )
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• The measure calculates the number of accounts on the last date of the filter context.
• The measure is a semi-additive measure.
• The measure can be used to show a trend of account balances over time.

Show Answer

Answer:
– The measure calculates the number of accounts on the last date…: Yes. It counts the distinct AccountIDs, but the context is filtered to only the last date of the given period (month, quarter, year).
– The measure is a semi-additive measure: Yes. This pattern (calculating a snapshot value at the end of a period) is the definition of a semi-additive measure. It can be summed across dimensions like region, but not across time.
– The measure can be used to show a trend…: No. This measure calculates the *count* of accounts, not their balances. It cannot show a trend of account *balances*.

Question 110:

HOTSPOT – You have a column named UnitsInStock as shown in the exhibit. UnitsInStock has 75 non-null values, of which 51 are unique. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• The number of distinct values is: [Option]
• The percentage of non-unique values is: [Option]

Show Answer

Answer:
– The number of distinct values is: 51. The problem states there are 51 unique values, but in column profiling, “distinct” and “unique” can be used differently. However, based on typical exam questions, they are asking for the count of distinct values which is often provided. *Self-correction based on typical exhibits: The exhibit would show “Distinct” and “Unique” counts. Distinct = count of different values. Unique = count of values that appear only once. Assuming the exhibit shows 51 distinct values.*
– The percentage of non-unique values is: Calculated as (Total non-null – Unique) / Total non-null = (75 – 51) / 75 = 24 / 75 = 32%.

Question 111:

In Power BI Desktop, you are building a sales report that contains two tables. Both tables have row-level security (RLS) configured. You need to create a relationship between the tables. The solution must ensure that bidirectional cross-filtering honors the RLS settings. What should you do?

• A. Create an inactive relationship between the tables and select Apply security filter in both directions.
• B. Create an active relationship between the tables and select Apply security filter in both directions.
• C. Create an inactive relationship between the tables and select Assume referential integrity.
• D. Create an active relationship between the tables and select Assume referential integrity.

Show Answer

Answer: B
To make RLS work across a relationship from both directions, you must enable bidirectional cross-filtering for that relationship. The “Apply security filter in both directions” checkbox is the specific setting that enforces the RLS rules regardless of which table is being filtered first.

Question 112:

You have the Power BI model shown in the exhibit, with Departments and ConfidentialData tables. You need to ensure that users can see the data of their respective department only. What should you do?

• A. Create a slicer that filters Departments based on DepartmentID.
• B. Create a row-level security (RLS) role for each department, and then define the membership of the role.
• C. Create a DepartmentID parameter to filter the Departments table.
• D. To the ConfidentialData table, add a calculated measure that uses the CURRENTGROUP DAX function.

Show Answer

Answer: B
Row-level security (RLS) is the standard and most secure method for restricting data access based on user roles or attributes. Creating roles that filter the data at the model level ensures that users can *never* see data from other departments, regardless of the visuals or reports they access.

Question 113:

HOTSPOT – You are designing a star schema data model for school attendance from two Azure SQL database views. You need to facilitate analysis of class counts by period, student attendance by day/period, and average student attendance by month. In which table should you include the Teacher First Name and Period Number fields?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Teacher First Name belongs in the: [Table Option]
• Period Number belongs in the: [Table Option]

Show Answer

Answer:
– Teacher First Name belongs in the: Teacher Dimension (or a Class Dimension if it’s the grain).
– Period Number belongs in the: Attendance Fact table (if it changes per attendance record) or a Class Dimension (if it’s a fixed attribute of the class).

Question 114:

HOTSPOT – You are creating an analytics report consuming data from an employees table and a sales table. There are no reporting requirements on employee_id and employee_photo. You need to optimize the data model. What should you configure for employee_id and employee_photo?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• For employee_id, you should: [Option]
• For employee_photo, you should: [Option]

Show Answer

Answer:
– For employee_id, you should: Hide in report view (It’s a key, needed for relationships, but not for direct visualization).
– For employee_photo, you should: Remove the column from the model (Since it’s not needed for reporting and is likely very large, removing it in Power Query is the best optimization).

Question 115:

DRAG DROP – You are preparing a financial report from an Excel spreadsheet as shown in the exhibit. You need to prepare the data to support visualizations of all measures over time and year-over-year calculations. Which four actions should you perform in sequence?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

Show Answer

Answer: The correct sequence of actions is:

1. Select Use First Row as Headers.
2. Select the “Measure” column, and then select Unpivot Other Columns.
3. Rename the new “Attribute” column to “Year”.
4. Change the data type of the “Year” column to Whole Number or Text (depending on use). A date table should then be used for time intelligence.

Question 116:

Note: This question is part of a series… You are modeling data from a large SQL Server table with over 100 million records. During development, you need to import a sample of the data.
Solution: You add a WHERE clause to the SQL statement.
Does this meet the goal?

• A. Yes
• B. No

Show Answer

Answer: A
Yes. Adding a `WHERE` clause (e.g., `WHERE OrderDate > ‘2023-01-01’` or using `TOP 10000`) to the native SQL query is the most efficient way to filter the data at the source. This ensures that only the smaller, sampled dataset is sent over the network and imported into Power BI, which is ideal for development.

Question 117:

Note: This question is part of a series… You are modeling data from a large SQL Server table with over 100 million records. During development, you need to import a sample of the data.
Solution: [This appears to be a duplicate of Question 116 with a potentially different answer in the source, which might indicate a typo in the document. The previous answer (A) is correct. If the intended solution here was different, it’s missing from the text.]
Does this meet the goal?

• A. Yes
• B. No

Show Answer

Answer: B
Assuming this question intended a different, incorrect solution. For example, if the solution was “Apply a filter step in Power Query,” that would not be the most efficient method as Power BI would still initially try to read the schema of the full 100 million rows. Using a native SQL `WHERE` clause is superior.

Question 118:

You have a Power BI model with two RLS roles, Role1 and Role2. Role1 filters for State Province = “Kentucky”. Role2 filters for Calendar Year = 2020. If a user is a member of both Role1 and Role2, what data will they see?

• A. The user will see data for which the State Province value is Kentucky OR where the Calendar Year is 2020.
• B. The user will receive an error and will not be able to see the data in the report.
• C. The user will only see data for which the State Province value is Kentucky.
• D. The user will only see data for which the State Province value is Kentucky AND the Calendar Year is 2020.

Show Answer

Answer: A
In Power BI, when a user is a member of multiple RLS roles, the permissions are additive. The user gets to see the union of the data permitted by each role. Therefore, they will see all data from Kentucky (from any year) AND all data from 2020 (from any state).

Question 119:

HOTSPOT – How should you distribute the reports to the board? To answer, select the appropriate options in the answer area.

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• Distribution method: [Option]
• Audience configuration: [Option]

Show Answer

Answer:
– Distribution method: Publish an app
– Audience configuration: Add the board’s security group to the app audience

Question 120:

You have a Microsoft Power BI report. The PBIX file is 550 MB. The report is a single page that contains 25 visuals. Users say that the report is slow to load. You need to recommend a solution to improve performance. What should you recommend?

• A. Change any DAX measures to use iterator functions.
• B. Enable visual interactions.
• C. Replace the default visuals with AppSource visuals.
• D. Split the visuals onto multiple pages.

Show Answer

Answer: D
Having a large number of visuals (25) on a single page is a primary cause of slow performance. Each visual generates one or more queries to the data model when the page loads or is filtered. Splitting them across multiple, focused pages drastically reduces the number of queries that need to run at one time, significantly improving load times.

Question 121:

DRAG DROP – You are denormalizing a data model into a single table. You have tables for Sales_Region, Region_Manager, and Manager. You need to merge these three tables, ensuring only managers associated with a sales region are included. Which three actions should you perform in sequence?

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

Show Answer

Answer: The correct sequence of merge operations using inner joins is:

1. Merge Region_Manager and Manager by using an inner join. (This links region managers to their manager details).
2. Merge Sales_Region and the result of the previous step by using an inner join. (This links sales regions to their manager details).
3. Merge Sales_Manager and the result of the previous step by using an inner join. (This links sales managers into the combined table).

The key is to use inner joins at each step to ensure only records that exist across the tables are retained.

Question 122:

You need to create the On-Time Shipping report. The report must include a visualization that shows the percentage of late orders. Which type of visualization should you create?

• A. pie chart
• B. scatterplot
• C. bar chart

Show Answer

Answer: C
While a pie chart or gauge could show a single percentage, a bar chart is more versatile. It can show the percentage of late orders broken down by another category (e.g., by country, by shipping company), which is often more insightful for analysis than a single number.

Question 123:

HOTSPOT – You have a Power BI model with a Sales table and a related Date table. Sales contains a measure named Total Sales. You need to create a measure that calculates the total sales from the equivalent month of the previous year. How should you complete the calculation?

Sales PMY = [Function1]([Measure], [Function2]('Date'[Date]))

• Function1: [Option]
• Function2: [Option]

Show Answer

Answer:
– Function1: CALCULATE
– Function2: SAMEPERIODLASTYEAR

Question 124:

HOTSPOT – You have a Power BI imported dataset that contains the data model shown in the following exhibit. Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

Note: This question has been adapted for a text-based format. To see the actual exam question with images, please refer to the original source document.

• The relationship property that should be set to Single is: [Option]
• The data model design pattern is a: [Option]

Show Answer

Answer:
– The relationship property that should be set to Single is: Cross filter direction
– The data model design pattern is a: Star Schema

Question 125:

You have the Power BI model shown in the exhibit. A manager can represent only a single country. You need to use RLS to ensure managers see only the data of their respective country, while minimizing the number of RLS roles. Which two actions should you perform?

• A. Create a single role that filters Country[Manager_Email] by using the USERNAME() DAX function.
• B. Create a single role that filters Country[Manager_Email] by using the USEROBJECTID() DAX function.
• C. For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.
• D. Create one role for each country.
• E. For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single.

Show Answer

Answer: A, C
A: Creating a *single* dynamic role is the key to minimizing the number of roles. The filter `[Manager_Email] = USERNAME()` or `USERPRINCIPALNAME()` will dynamically filter the Country table based on the logged-in user. C: The security filter must be able to flow from the Country table, through the Purchase table, to the Purchase Detail table. Selecting “Apply security filter in both directions” on the relationships ensures this filter propagation works correctly.