PL-300 practice questions 161–170

1. Question 161 · 358 in bank

   You have a Power BI model that contains a table named Sales. The Sales table contains the following columns: • Order Line ID • Product ID • Unit Price • Order ID • Quantity Orders are uniquely identified by using the order ID and can have multiple order lines. Each order line within an order contains a different product ID. You need to write a DAX measure that counts the number of orders. Which formula should you use?

   • ACount('Sales'[Order ID])
   • BCountA('Sales' [Order ID])
   • CCountRows('Sales')
   • DDistinctCount('Sales' [Order ID])

   Show answer

   Answer: D

   Explanation

   "Orders are uniquely identified by using the order ID and can have multiple order lines" - I think the important statement is "and can have multiple order lines" which means that the order ID can appear more than once in the table if the order contains more than one products - so I think the answer is correct.

   Source: Adapted from community discussion — Maria86

   Next question

2. Question 162 · 358 in bank

   Note: This section contains one or more sets of questions with the same scenario and problem. Each question presents a unique solution to the problem. You must determine whether the solution meets the stated goals. More than one solution in the set might solve the problem. It is also possible that none of the solutions in the set solve the problem. After you answer a question in this section, you will NOT be able to return. As a result, these questions do not appear on the Review Screen. You are creating a Power BI semantic model named Model1 that contains the following tables.

   

   The tables have the following relationships.

   

   You have 100 salespeople in 10,000 cities. Each salesperson sells in multiple cities. You import one million rows into the Sales table. You need to reduce the size of Model1. The solution must ensure that you can analyze the total number of sales by salesperson per city. Solution: You remove the Salesperson table. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: B

   Next question

3. Question 163 · 358 in bank

   HOTSPOT - You are creating a Power BI model in Power BI Desktop. You need to create a calculated table named Numbers that will contain all the integers from -100 to 100. How should you complete the DAX calculation? 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

   Correct Answer To create a calculated table named Numbers in Power BI Desktop that contains all the integers from -100 to 100, you can use the following DAX calculation: Numbers = GENERATESERIES(-100, 100, 1) Explanation: The GENERATESERIES function generates a table of values that starts at the first argument (-100), ends at the second argument (100), and increments by the third argument (1) in this case. The resulting table will contain all the integers from -100 to 100 inclusive. The calculated table is named "Numbers" and is created by assigning the output of the GENERATESERIES function to it using the "=" operator. No confusion, and no need to discuss further

   Source: Adapted from community discussion — SanaCanada

   

   Next question

4. Question 164 · 358 in bank

   Select 2

   In Power Query Editor, you have a query named Sales Data that contains the following columns.

   

   You need to create two queries named Product Dimension and Sales Fact based on the Sales Data query. The solution must minimize maintenance effort and the size of the dataset. Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

   • AReference the Sales Data query to create the new queries.
   • BDisable the load for the Sales Fact query.
   • CDuplicate the Sales Data query to create the new queries.
   • DClear Include in report refresh for the Sales Data query.
   • EDisable the load for the Sales Data query.

   Show answer

   Answer: AE

   Explanation

   Please be responsible. Comment and vote carefully. The answer is A&E. To choose reference or duplicate or copy, the points are: 1: Reference, duplicate, copy queries will all make the file bigger to a similar size. 2: Choose Reference if you want the referenced queries to change when the original query changes. Choose Duplicate or Copy if you don’t want that. Reference queries is more common in many real-world scenarios because the fact table is always changing. 3: Copy and Duplicate copy the transformation steps but reference not. Disable the load for the Sales Data query will : (1) It will be cleaner in Power BI desktop because you won’t see the Sales data query. (2) improve performance. The star schema is normally the best in Power Bi modelling because of the performance. And this is the reason why we need to separate the main table into different tables(Normalization). The main table itself should not be included in a star scheme.

   Source: Adapted from community discussion — Pocu

   Next question

5. Question 165 · 358 in bank

   You have a Power BI model that contains a table named Date. The Date table contains the following columns: • Date • Fiscal Year • Fiscal Quarter • Month Name • Calendar Year • Week Number • Month Number • Calendar Quarter You need to create a calculated table based on the Date table. The calculated table must contain only unique combinations of values for Calendar Year, Calendar Quarter, and Calendar Month. Which DAX function should you include in the table definition?

   • AADDCOLUMNS
   • BCALCULATE
   • CSUMMARIZE
   • DDATATABLE

   Show answer

   Answer: C

   Explanation

   I wanna say that again. Comment and vote carefully before you have actually done some research otherwise It will cause confusion to other people. The answer is Summarize. UniqueCalendarTable = SUMMARIZE( Date, 'Date'[Calendar Year], 'Date'[Calendar Quarter], 'Date'[Month Name] )

   Source: Adapted from community discussion — Pocu

   Next question

6. Question 166 · 358 in bank

   Note: This section contains one or more sets of questions with the same scenario and problem. Each question presents a unique solution to the problem. You must determine whether the solution meets the stated goals. More than one solution in the set might solve the problem. It is also possible that none of the solutions in the set solve the problem. After you answer a question in this section, you will NOT be able to return. As a result, these questions do not appear on the Review Screen. You are creating a Power BI semantic model named Model1 that contains the following tables.

   

   The tables have the following relationships.

   

   You have 100 salespeople in 10,000 cities. Each salesperson sells in multiple cities. You import one million rows into the Sales table. You need to reduce the size of Model1. The solution must ensure that you can analyze the total number of sales by salesperson per city. Solution: You summarize the data in the Sales table by SalesPersonID. Does this meet the goal?

   • AYes
   • BNo

   Show answer

   Answer: B

   Next question

7. Question 167 · 358 in bank

   HOTSPOT - You have a Power BI model that contains the following data.

   

   The Date table relates to the Sales table by using the Date columns. You need to create a calculated table that will contain the following: • A row for each year • A column that contains the total sales per year How should you complete the DAX calculation? 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've recreated mock data through ChatGPT and in PowerBI. I've tested the following dax measure : - SalesSummary = SUMMARIZE(Sales,Date[Date],"Sales",SUM(Sales[Sales])) - SalesSummary = SUMMARIZE(Sales,Date[Year],"Sales",SUM(Sales[Sales])) First dax measures creates a table that has many rows as many sales in that month have been sold and does not respond to "A row for each year" requirement. By using Date[Year], you have a summarized Table which you have ONLY one row per each year with the value as sum Something like this : 2024 | 8000,00$ 2023 | 5000,00$ Rollup and Select Columns in first menu are wrong as they don't match parameters. So, Correct answer is *SalesSummary = SUMMARIZE(Sales,Date[Year],"Sales",SUM(Sales[SalesAmount]))* (don't mind the names used for mockup data)

   Source: Adapted from community discussion — SomeAzureGuyincube

   References

   • Microsoft Learn

   

   Next question

8. Question 168 · 358 in bank

   You have a Power BI model that contains several tables. Each table contains multiple columns. You discover that some columns are redundant and are NOT required for reporting. You need to remove the redundant columns. The solution must meet the following requirements: • Ensure that the model remains usable for reporting. • Minimize administrative effort. What should you do?

   • AFrom the Data pane, select the redundant columns, and then select Delete from model.
   • BFrom the Model pane, right-click the tables that contain the redundant columns, and then select Hide in report view.
   • CFrom Power Query Editor, remove the redundant columns.
   • DFrom the Data pane, select the redundant columns, and then select Hide in report view.

   Show answer

   Answer: C

   Next question

9. Question 169 · 358 in bank

   You use Power Query Editor to import and preview sales data from the years 2020 and 2021 in a Microsoft Excel file as shown in the following exhibit.

   

   You need to shape the query to display the following three columns: • Month • Sales • Year What should you select in Power Query Editor?

   • AMerge columns
   • BTranspose
   • CUnpivot columns
   • DPivot column

   Show answer

   Answer: C

   Explanation

   C is correct. To shape the query to display the required three columns (month, sales,year), you should select the "Unpivot columns" option in Power Query Editor. This will transfer the data from its current columnar structure (where each year is a separate column) into a tabular format with separate rows for each combination of Month, Sales, and Year.

   Source: Adapted from community discussion — Igetmyrole

   Next question

10. Question 170 · 358 in bank

    HOTSPOT - You are creating a Power BI model to analyze inventory. You load data into three tables named Date, Product, and Inventory. The Inventory table relates to the Date and Product tables by using one-to-many relationships. Inventory data is recorded daily with no exceptions. The correct inventory quantity for a given product in a month is the last recorded value for that month. You need to write a DAX measure that will show the correct inventory value when a user analyzes inventory by year, month, or date. How should you complete the measure? 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

    Calculate LastNonBlankValue LASTDATE will NOT work. Date - is a date table and it contains all days of the calendar month continuously so it will always return the last day of the month even if there is no inventory records. "The correct inventory quantity for a given product in a month is the last recorded value for that month.". That's why we should use LastNonBlankValue instead.

    Source: Adapted from community discussion — andrewkravchuk97

    References

    • Microsoft Learn

    

    Next question

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

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