PL-300 practice questions 181–190

1. Question 181 · 358 in bank

   You have a Power BI model that contains a table named Employees. The table contains the following columns: • Employee ID • First Name • Last Name • Department • Salary Each employee is uniquely identified by using Employee ID. You need to create a DAX measure that will calculate the average salary of all the employees in the sales department. Which DAX expression should you use?

   • ADISTINCTCOUNT(‘Employees’[Salary])
   • BCALCULATE(DISTINCTCOUNT(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
   • CCALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
   • DAVERAGE(‘Employees’[Salary])

   Show answer

   Answer: C

   Explanation

   DISTINCTCOUNT as in Option A is definitively not calculating any averge but just how many different salaries there are in the table Similar, option B is calculation how many different salaries there are in the "Sales" Deptm Option D is calculating the total salary average in the company not just for the "Sales" department (and it could be affected by CONTEXT filters) In option C we are calculating the AVERAGE of the Salary, just for employees in SALES department and encapsulating calculation in CALCULATE to remove any report filter

   Source: Adapted from community discussion — jaume

   Next question

2. Question 182 · 358 in bank

   Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution. After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen. You have a Power BI report that imports a date table and a sales table from an Azure SQL database data source. The sales table has the following date foreign keys: • Due Date • Order Date • Delivery Date You need to support the analysis of sales over time based on all the date foreign keys. Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables. You create active relationships between the sales table and each date table. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: A

   Explanation

   I would have thought the Answer is yes as you create the other required tables using the dax function and create relationships to the required dates

   Source: Adapted from community discussion — Tex02

   Next question

3. Question 183 · 358 in bank

   You have a Power BI semantic model that contains item, price, and country data. The data is displayed in a report that uses filters. You need to calculate the average item price for a given country. The solution must support the existing filters. Which type of quick measure should you use?

   • ATotal for category (filters applied)
   • BAverage per category
   • CRolling average
   • DWeighted average per category

   Show answer

   Answer: B

   Explanation

   This is the only solution calculating the average as requested. Other options are calculating "rolling" and "weighted" average or total price. Since ew must keep existing filters, there is no need to use CALCULATE function there

   Source: Adapted from community discussion — jaume

   References

   • Microsoft Learn

   Next question

4. Question 184 · 358 in bank

   You use Power Query Editor to preview a query that contains sales order data in the following columns: • Tax Amount • Sales Order ID • Freight Amount • Subtotal Amount • Total Item Quantity The Sales Order ID column uniquely identifies each sales order. The Subtotal Amount and Total Item Quantity columns are always populated, but the Tax Amount and Freight Amount columns are sometimes null when an order has no associated amount. You need to query the data to identify the following metrics by month: • The average item quantity per order • The average freight amount per order • The maximum item quantity per order How should you modify the query?

   • AIn the Total Item Quantity column, replace the null values with 0.
   • BIn the Tax Amount column, remove rows that contain null values.
   • CIn the Freight Amount column, remove rows that contain null values.
   • DIn the Freight Amount column, replace the null values with 0.

   Show answer

   Answer: D

   Explanation

   D is the correct answer. Removing rows is not the solution. Item Quantity is always present. Replace Freight Amount with 0 if it is null

   Source: Adapted from community discussion — rajukg

   Next question

5. Question 185 · 358 in bank

   DRAG DROP - You use Power Query Editor to import three tables named Customers, Address, and Country. In the source system, not every customer has a related address, but every address has a related country. You need to merge all the tables into a single query. The solution must optimize query refresh performance. Which type of join should you use for each merge operation? To answer, drag the appropriate join types to the correct operations. Each join type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Left outer join is correct because we cant ignore the customers who does not have a address. Left table (Customers) should be included. 1. Left Outer Join 2. Inner Join

   Source: Adapted from community discussion — Arth_1111

   

   Next question

6. Question 186 · 358 in bank

   You have a Power BI semantic model that contains four queries named Query 1, Query2. Query3, and Query4. Query1 loads customer data into the model and is referenced by the other three queries. You discover that data refresh for the model is slow. You need to improve the data refresh time. The solution must minimize costs. What should you do?

   • ARun the Table.buffer function in Query1.
   • BDuplicate Query1 to all the other queries.
   • CReconfigure Query1 as a dataflow entity.
   • DFrom the Power BI Admin portal, increase the Capacity settings.

   Show answer

   Answer: C

   Explanation

   https://learn.microsoft.com/en-us/power-bi/guidance/power-query-referenced-queries "In the example, if Query1 is redesigned as a dataflow entity, Query2, Query3, and Query4 can use it as a data source. With this design, the entity sourced by Query1 will be evaluated only once."

   Source: Adapted from community discussion — b92fc92

   References

   • Microsoft Learn

   Next question

7. Question 187 · 358 in bank

   HOTSPOT - You have an organization dimension named DimOrganizations. You have four related tables as 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: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   The answer is correct. It is a snowflakeschema and DimOrgSubVertical is the only table that supports a hierarchy model for DimOrgVertical

   Source: Adapted from community discussion — rcaliandro

   

   Next question

8. Question 188 · 358 in bank

   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: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: Assume Referential Integrity When connecting to a data source using DirectQuery, you can use the Assume Referential Integrity selection to enable running more efficient queries against your data source. This feature has a few requirements of the underlying data, and it is only available when using DirectQuery. Note: The following requirements are necessary for Assume referential integrity to work properly: Data in the From column in the relationship is never Null or blank For each value in the From column, there is a corresponding value in the To column Box 2: Star schema - Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either dimension or fact. Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a very large number of rows and continue to grow over time. Example: Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-assume-referential-integrity https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

   Source: Adapted from community discussion — Guru1337

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

9. Question 189 · 358 in bank

   HOTSPOT - You have a Power BI semantic model that contains a table named Opportunity. The Opportunity table contains a column named Qualification. The Qualification column contains values between 0 and 1. You need to build a new measure to score the opportunities on a scale of low. medium, and high. How should you complete the DAX formula? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   I will use IF instead of IF.EAGER due to values that are used in true/false arguments. If there would be more complex values (another formulas, references to columns etc.) I will prefer IF.EAGER. See https://learn.microsoft.com/en-us/dax/if-eager-function-dax -cdot above Examples part.

   Source: Adapted from community discussion — miloszmiloszz

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

10. Question 190 · 358 in bank

    You have a Power BI semantic model that connects to a streaming data source. The data source is updated frequently. You need to create a Power BI report that meets the following requirements: • Supports real-time analytics • Minimizes performance impact on the data source • Displays the most recent data without performing a data refresh Which connectivity mode should you use for the dataset?

    • ADirectQuery mode
    • Bimport mode
    • CLiveConnect mode
    • Dpush mode

    Show answer

    Answer: D

    Explanation

    D. push mode https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data Push semantic models don't contain a formal definition of a data source, so they **don't require you to perform a data refresh in Power BI**. You refresh them by pushing your data into the semantic model through an external service or process, such as Azure Stream Analytics. This is a **common approach for real-time analytics** with Power BI.

    Source: Adapted from community discussion — Pritch

    References

    • Microsoft Learn

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

PL-300 practice, question 181. 358 questions available.