PL-300 Exam – Practice 8 Welcome to your PL-300 Exam - Practice 8 Question 1 Consider the Power BI model shown in the picture. Product Table has a relationship to the Product Category Table on the SKU column.Here, you can check a sample of data inside the tables:Product Table:SKU: HD-001, Product: GymSmartwatch, Color: BlueSKU: HD-002, Product: XSound Headphones, Color: BlackSKU: HD-003, Product: Laptop Pro 2000, Color: GreenProduct Category Table:SKU: HD-001, Category: SmartwatchSKU: HD-002, Category: HeadphoneSKU: HD-003, Category: ComputerYou need to use in your Power BI report a Hierarchy that shows two levels: product and category.Solution: Create a DAX expression that uses CONCATENATEX function between the Product column and the Category column.Does this solution meet the goal? Yes No Question 2 Your database contains the tables shown in the picture: You plan to create a chart that displays total Enrollments[Total Paid] by Course[Name]. Before creating the visualization, you need to modify the model.Which action should you perform? There are two right options. Create a relationship between the Enrollments table and the Courses table. Then, to the Enrollments table, add a column that uses the RELATED('Courses'[Course ID]) DAX formula. To the Enrollments table, add a measure that uses the COUNTX('Enrollments'[Total Paid]) DAX formula. To the Enrollments table, add a measure that uses the COUNTA('Enrollments'[Enrollment ID]) DAX formula. Question 3 Suppose you have a Power BI model based on the following two tables:– Enrollments (columns name: Enrollments_ID, Enrollments_amount, DateID)– Date (columns name: DateID, Date, Month, Week, Year)There is an active relationship between Enrollments and Date.You want to create a measure to calculate the Enrollments for the same period from the previous year.Select the right DAX formula. SUM(Enrollments[Enrollments_amount]) / CALCULATE(SUM(Enrollments[Enrollments_amount]), SAMEPERIODLASTYEAR('Date'[Date])) SUM(Enrollments[Enrollments_amount]) - CALCULATE(Enrollments[Enrollments_amount]), DATESYID('Date'[Date])) CALCULATE(SUM(Enrollments[Enrollments_amount]), SAMEPERIODLASTYEAR('Date'[Date])) CALCULATEx(SUM(Enrollments[Enrollments_amount]), DATEADD('Date'[Date], -1, YEAR)) Question 4 Consider the following two tables:– Sales (columns: SalesID, OrderDate, Salesman)– Date (columns: Date, Month, Week, Year)There is a relationship between Sales[OrderDate] and Date[Date].Suppose you want to create a goal for the number of sales made in the current year and set it as 10% higher than the number of sales made in the previous year.Select the proper order of the DAX functions to complete correctly the following formula: CALCULATE - COUNT - PREVIOUSYEAR COUNTX - FORMAT - DATEADD CALCULATE - SUMX - PREVIOUSYEAR COUNT - CALCULATE - SAMEPERIODLASTYEAR Question 5 You plan to create a DAX formula to filter the total enrollments of the 2019 year.How should you complete the formula shown in the picture, in order to effectively use it apart from the other date filter set on the report page? ALL FORMAT VALUES ALLEXCEPT Question 6 Your database contains the tables shown in the picture: Check the information stored in the following columns:– Date[Month] in the mmyyyy format– Date[Date_ID] in the ddmmyyyy format– Date[Date_name] in the mm/dd/yyyy format– Monthly Drop out[Month_ID] in the mmyyyy formatThe only relationship is between the Course table and the Monthly Drop Out table by the Course ID Column.After you have imported the database in PowerBI, you need to calculate the number of enrollments.Which option meets the goal? Create a calculated measure that uses the SUMX(Enrollment ID DAX formula. Create a calculated column that uses the SUM(Enrollment ID) DAX formula. Create a calculated measure that uses the COUNTA(Enrollment ID) DAX formula. Create a calculated column that uses the COUNTA(Enrollment ID) DAX formula. Question 7 You plan to create a measure named YTDPreviousEnrollments that will be used in a table visualization. YTDPreviousEnrollments must show the year-to-date (YTD) enrollment amount of the previous year for the same month.Check the picture for a sample of the desired data:Select the proper order of DAX formula targets that calculate the YTDPreviousEnrollments. CALCULATE - TOTALYTD CALCULATE - DATEADD CALENDAR - DATEADD FILTER - DATEDIFF Question 8 Consider the table named Enrollments shown in the picture:You need to create the following measure:Total Enrollments This Year = SUM([Enrollments])Suppose you want to compare the current yearly enrollments to the previous year by another measure.Complete the following DAX formula with the right targets: SUMX - PREVIOUSYEAR DATEADD - CALCULATE CALCULATE- DATEADD CALCULATE - SAMEPERIODLASTYEAR Question 9 You have a Power BI model for sales data. You create a measure to calculate the year-to-date sales. You need to compare the year-to-date sales with the previous year for the same time period.Which DAX function should you use? DATEADD ENDOFYEAR TOTALYTD DATESYTD Question 10 Suppose you have a Power BI model based on the following two tables:– Enrollments (columns: Enrollments_ID, Enrollments_date, Enrollments_amount, CourseID)– Courses (columns: CourseID, Course_name)There is an active relationship between Enrollments and Courses.You want to create a measure to rank the courses based on their total enrollment amount.Select the right DAX formula. RANK(ALL('Courses' [Course_ID]), SUMX(RELATEDTABLE(Enrollments), [Enrollments_amount])) RANKX(ALL(Enrollments), SUMX(RELATEDTABLE(Courses), [Enrollments_amount])) RANKX(ALL(Courses), SUMX(RELATEDTABLE(Enrollments), [Enrollments_amount])) TOPN(ALL('Courses' [Course_ID]), SUMX(RELATEDTABLE(Sales), [Enrollments_amount])) Question 11 You plan to join a fact table named ActivityLog to a date table dimension named ActivityDate.The date value in ActivityLog is a DateTime column named ActivityStart. The date value in ActivityDate is a number column named DateID. DateID is in the YYYYMMDD format.What should you do in the model before you create the relationship? Change the Data Type of ActivityStart to Date. Create a measure in ActivityLog that uses the FORMAT DAX function. Create a calculated column in ActivityLog that uses the FORMAT DAX function. Change the Data Type of DateID to Date. Question 12 You have a Power BI model that contains two tables named Enrollments and Date. Enrollments table contains three columns named EnrollmentsAmount, EnrollmentDate and EnrollmentID. You want to create a measure to sum the last 12 months of enrollments.Note: you have to start from the last date an enrollment was made and reject any filters set on the report.Select the proper order of the DAX functions to complete correctly the following formula: ALLEXCEPT - DATEADD CALCULATE - DATESYTD DATEADD - LASTNONBLANK CALCULATE - DATEADD Question 13 You want to see the sum of the Sales[SalesAmount] for the Contoso brand by creating a new Quick Measure.How you are supposed to set the following fields? Calculation: Running totals, Base Value: Count of Sales Amount, Field: Contoso, Direction: Ascending Calculation: Filtered Value, Base Value: Count of Sales Amount, Filter: BrandName, Select a value: Contoso Calculation: Running totals, Base Value: Sum of Sales Amount, Field: Contoso, Direction: Ascending Calculation: Filtered Value, Base Value: Sum of Sales Amount, Filter: BrandName, Select a value: Contoso Question 14 Consider the following scenario: You need to create a relationship between the Monthly Drop out table and Date[Date ID].What should you do before you create the relationship? In the Monthly Drop Out table, create a new calculated column named Date_ID that uses the ddmmyyyy format. In the Date table, create a new calculated column named Month_ID that uses the yyyydd format. To the Date table, add a calculated column that uses the RELATED(Monthly Drop Out[Month_ID]) DAX formula To the Order table, add a calculated column that uses the RELATED(Monthly Drop Out[Month_ID]) DAX formula. Question 15 Consider the table shown in the picture:– Monthly Appointments[Month] is in the mmyyyy format;– Monthly Appointments[Date_name] is in the mm/dd/yyyy format.You plan to display the month as the following ‘jan2017’.To meet the goal, you must add a calculated column. Select the proper order of the DAX functions to complete correctly the following formula: CONCATENATEX - MMM CONCATENATE - MMM COMBINE - MMM CONCATENATE - MM Question 16 Consider the tables shown in the picture:You need to create a new table that displays the top 5 Students by the total of attended courses.Select the proper order of the DAX functions to complete correctly the following formula: CALCULATE - TOPN CALCULATE - VALUES FILTER - RANKX CALCULATE - RANKX Question 18 You want to create a bar chart visualization to show the count of course enrollments by year that have an Enrollments Amount greater than 1,000. You need to create a measure that will be used in the bar chart visualization.Select the proper order of the DAX functions to complete correctly the following formula: COUNT - VALUE COUNTROWS-FILTER CALCULATE - COUNT CALCULATE - FILTER Question 19 Consider a PowerBI model with two tables named Enrollments and Date. Enrollments contains five columns named Student ID, TotalPaid, EnrollmentDate, AttendanceDate, and StartingDate. Date Table contains only one column named Date.The tables have the following relationships:– Enrollments[EnrollmentDate] and Date[Date]– Enrollments[AttendanceDate] and Date[Date]– Enrollments[StartingDate] and Date[Date]The active relationship is on Enrollments[EnrollmentDate] .You plan to create measures to count both the number of Enrollments by [AttendanceDate] and the Enrollments by [StartingDate].NOTE: You can’t meet the goal by duplicating data or loading additional data.Solution: You create measures that use the CALCULATE, COUNT, and USERELATIONSHIP DAX functions.Does this solution resolve the issue? Yes No Question 20 Consider you want to add a date table named Date to your Power BI model. That table will contain the following columns: date, year, month and it has to include the following date range:from 1st January 2018 to 31st December 2019.Select the right DAX functions that must be included in the formula. DATE CALENDAR, DATE CALENDAR, YEAR, MONTH CALENDARAUTO, YEAR, MONTH Question 21 You have a measure that calculates the year-to-date sales. You need to compare the year-to-date sales with the previous year for the same time period.Select the right DAX function for the second purpose. DATESVTD LASTDATE SAMEPERIODLASTYEAR DATEADD TOTALYTD PREVIOUSYEAR Question 22 You are creating appointment schedules for your clients. Consider the following data from a query named Appointments:You need to visualize the data as shown in the following picture by using a matrix visualization:Select the proper order of DAX formula targets which allows you to create the measure that will display the checkboxes. COUNTX - UPPER COUNTROWS-UNICHAR SUM - LOWER CALCULATE - UNICHAR Question 23 Consider the following sample data: You want to create a calculated column to display the date in the following format: January 01, 2020.Select the right DAX formula. FORMAT([Date], "MMM") || FORMAT([Date], "DD") || FORMAT([Date], "YYYY") FORMAT('Date_Table' [Date], "MMM") || FORMAT('Date_Table' [Date], "DD") || FORMAT('Date_Table' [Date], "YYYY") FORMAT([Date], "MMMM DD, YYYY") FORMAT([Date], "M") || FORMAT([Date], "D") || FORMAT([Date], "Y") Question 24 You are modeling data by using Microsoft Power Bl. Part of the data model is a large Microsoft SQL Server table named [Fact Internet Sales] that has more than 500 million records. During the development process, you need to import a sample of the data from the [Fact Internet Sales] table.Solution: You write a DAX expression that uses the FILTER function.Does this meet the goal? Yes No Question 25 You are modeling data by using Microsoft Power Bl. Part of the data model is a large Microsoft SQL Server table named [Fact Internet Sales] that has more than 500 million records. During the development process, you need to import a sample of the data from the [Fact Internet Sales] table.Solution: You add a report-level filter that filters based on the [Fact Internet Sales] date.Does this meet the goal? Yes No Question 26 You have a prospective customer list that contains 1,500 rows of data. The list contains the following fields:*First name*Last name*Email address*State*Region*Phone numberYou import the list into Power Query Editor.You need to ensure that the list contains records for each State to which you want to target a marketing campaign.Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point. Open the Advanced Editor. Select Column quality. Enable Column profiling based on entire dataset. Select Column distribution. Select Column profile. Question 27 You have the dataset shown below You need to ensure that the visual shows only the 5 cities that have the highest sales profit.What should you do? Add a calculated column to the table that uses the TOPN function. In the visual, replace Sales with the calculated column. Add a calculated column to the table that returns the city name if the city is in the top 5, otherwise the calculated column will return "Not in Top 5". In the visual, replace Sales with the calculated column. Add a Top N filter to the visual. Configure the Sales measure to use the RANKX function. Question 28 DRAG DROP –You are creating a dataset from a JSON file that contains an array of documents.You need to import attributes as columns from all the documents in the JSON file. The solution must ensure that date attributes can be used as date hierarchies in Microsoft Power BI reports.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order. 1. Expand the records 2. Add columns that use data type conversion 3. Convert the list to table 1. Convert the list to table 2. Expand the records 3. Set the data types 1. Expands the Columns 2. Expand the records 3. Set the data types 1. Convert list to Table 2. Expand Column 3. Set Date type 1. Expand the records 2. Convert the list to table 3. Expands the Columns Question 29 You are creating an analytics report that will consume data from the tables shown in the following table.There is a relationship between the tables. There are no reporting requirements on [emp_id] and [employee_photo]. You need to optimize the data model. What should you configure for [emp_id] and [employee_photo] column? Box 1. emp_id : delete Box 2. employee_photo : delete Box 1. emp_id : hide Box 2. employee_photo : delete Box 1. emp_id : hide Box 2. employee_photo : hide Box 1. emp_id : delete Box 2. employee_photo : hide Question 30 You have the visual shown below You need to show the relationship between [Sales Amount] , [Total Product cost] By [category name] and [year].What should you do? Add a slicer for the year From the Analytics pane, add an median line Create a DAX measure that calculates YTD growth Add a play axis for Scatter plot visual Question 31 You have two Azure SQL databases that contain the same tables and columns. For each database, you create a query that retrieves data from a table named Customers. You need to combine the Customer tables into a single table. The solution must minimize the size of the data model and support the scheduled refresh in powerbi.com.What should you do? Box 1: Merge queries Box 2: Disable including the query in report refresh Box 1: Append queries Box 2: Disable including the query in report refresh Box 1: Append queries as New Box 2: Disable including the query in report refresh Box 1: Append queries as New Box 2: Disable loading the query to the data model Box 1: Merge queries as New Box 2: Disable loading the query to the data model Question 32 You need to configure the visual as shown below What will you add to the visual? a measure a trendline a forecast an Average line Question 33 You have a large dataset that contains more than 10 million rows. The table has a DateTime column named Date. You need to reduce the size of the data model. What should you do? Trim the Date column Split the Date column into two columns, one that contains only the time and another that contains only the date. Round the hour of the Date column to start of Hour. Change the data type of the Date column to Text Question 34 Your company has employees in 50 states. The company recently decided to associate each state to one of the following three regions: East, West, and South. You have a data model that contains employee information by state.The model does NOT include region information. You have a report that shows the employees by state.You need to view the employees by region as quickly as possible.What should you do? Create a new aggregation that summarizes by state. Create a new group on the state column and set the Group type to List Create a new group on the state column and set the Group type to Bin Question 35 You have a line chart that shows the number of employees in a department over time. You need to see the total salary costs of the employees when you hover over a data point. Add a salary to the tooltips. Add salary to the drillthrough fields. Add a salary to the visual filters. Question 36 You open a query in Power Query Editor. You need to identify the percentage of empty values in each column as quickly as possible. Which Data Preview option should you select? Column quality Column profile Column distribution Show whitespace Question 37 You have a table that contains sales data and approximately 1,500 rows. You need to identify outliers in the table. Which type of visualization should you use? area chart donut chart scatter plot pie chart Question 38 You create the following step by using Power Query Editor = Table.ReplaceValue(SalesLT_Address,”1318″,”1319″,Replacer.ReplaceText,{“AddressLine1”}) A row has a value of 21318 Lasalle Street in the AddressLine1 column. What will the value be when the step is applied? 1319 21319 Lasalle Street 1318 21318 Lasalle Street Question 39 What is the benefit of using a report tooltip? To give users additional information about a report visual, such as the author name and modified date To provide additional detail that is specific to the context of the data that is being hovered over. To give users the ability to export data from the visual. Question 40 You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date and time each complaint occurred. The data in Logged is in the following format: 2019-12-31 at 09:59. You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.What should you do? Change the data type of the Logged column to Date Apply a transform to extract the last 11 characters of the Logged column and set the data type of the new column to Date. Create a column by example that starts with 2019-12-31 and set the data type of the new column to Date Use Split by delimiter option to split date and time by providing “at” delimiter. Question 41 You have a Microsoft Power Bl report. The size of the PBIX file is 1024 MB. The report is accessed by using an App workspace is a shared capacity of powerbi.com. The report uses an imported dataset that contains one fact table. The fact table contains 12 million rows. The dataset is scheduled to refresh twice a day at 09:00 and 18:00. The report is a single page that contains 15 custom visuals and 10 default visuals. Users say that the report is slow to load the visuals when they access and interact with the report You need to recommend a solution to improve the performance of the report. What should you recommend? Split the visuals onto multiple pages. Replace the default visuals with custom visuals Increase the number of times that the dataset is refreshed. Remove the unused column from the model Question 42 You need to create a visualization that compares revenue and cost over time. Which type of visualization should you use? stacked area chart donut chart line chart waterfall chart Question 43 You are developing a report page. Some users will navigate the report by using a keyboard, and some users will consume the report by using a screen reader. You need to ensure that the users can consume the content on a report page in a logical order.What should you configure in Microsoft Power BI Desktop? the tab order the layer order the bookmark order the X position Question 44 What does the decomposition tree not enable you to do? Conduct root cause analysis to understand a measure better. Conduct what-if analysis with built-in parameters. Automatically analyze selected dimensions to find where a measure is highest or lowest. Question 45 You have a report that contains a bar chart and a column chart. The bar chart shows customer count by customer segment. The column chart shows sales by month. You need to ensure that when a segment is selected in the bar chart, you see which portion of the total sales for the month belongs to the customer segment.How should the visual interactions be set on the column chart when the bar chart is selected? filter Highlight No impact Question 46 Where can you configure and set data alerts? Data alerts can be set only in Power BI service on specific visuals such as KPI cards, gauges, and cards. Data alerts can be set only in Power BI desktop on specific visuals such as KPI cards, gauges, and cards. Data alerts can be set only in Power BI desktop and Power BI service on specific visuals such as KPI cards, gauges, and cards. Question 47 Your company has training videos that are published to Microsoft Stream. You need to surface the videos directly in a Microsoft Power BI dashboard. Which type of tile should you add? Video custom streaming data text box web content Question 48 Is it possible to create a relationship between two columns if they are different DATA TYPE columns? Yes No Question 49 You have a custom connector that returns ID, From, To, Subject, Body, and Has Attachments for every email sent during the past year. More than 10 million records are returned. You build a report analyzing the internal networks of employees based on whom they send emails. You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size.What should you do? Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent. Remove the Subject and Body columns during the import. From Model view, set the Subject and Body columns to Hidden. Question 50 You have sales data in a star schema that contains four tables named Sales, Customer, Date, and Product. The Sales table contains purchase and ship dates. Most often, you will use the purchase date to analyze the data, but you will analyze the data by both dates independently and together.You need to design an imported dataset to support the analysis. The solution must minimize the model size and the number of queries against the data source.Which data modeling design should you use? On the Date table, use a reference query in Power Query and create active relationships between Sales and both Date tables in the modeling view. Duplicate the Date query in Power Query and use active relationships between both Date tables. Use the Auto Date/Time functionality in Microsoft Power BI and do NOT import the Date table. Create an active relationship between Sales and Date for the purchase date and an inactive relationship for the ship date. Question 51 You are creating a visual to show the ranking of product categories by sales revenue. Your company’s security policy states that you cannot send data outside of your Microsoft Power Bl tenant Which approach provides the widest variety of visuals while adhering to the security policy? Use default or any custom visuals from the marketplace. Use default or certified custom visuals. Use default visuals or custom visuals uploaded from a .pbiviz file. Use only default visuals Question 52 You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as a numerical amount representing US dollars.You need to create a reference line to show which employees are above the median salary.Solution: You create an average line by using the Salary measure.Does this meet the goal? Yes No Question 53 You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as a numerical amount representing US dollars.You need to create a reference line to show which employees are above the median salary.Solution: You create a percentile line by using the Salary measure and set the percentile to 50%.Does this meet the goal? Yes No Question 54 You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the axis. Salary is present in the data as a numerical amount representing US dollars.You need to create a reference line to show which employees are above the median salary.Solution: You create a constant line and set the value to .5.Does this meet the goal? Yes No Question 55 What visual should be used to display outliers? line chart scatter chart clustered column chart Question 56 You are modeling data by using Microsoft Power Bl. Part of the data model is a large Microsoft SQL Server table named [Fact Internet Sales] that has more than 500 million records. During the development process, you need to import a sample of the data from the [Fact Internet Sales] table.Solution: You add a WHERE clause to the SQL statement.Does this meet the goal? Yes No Question 57 What can be achieved by removing unnecessary rows and columns? Deleting unnecessary rows and columns will reduce a dataset size and its good practice to load only necessary data into your data model. It is not necessary deleting unnecessary rows and columns and it is a good practice to keep all metadata intact. Deleting unnecessary rows and columns can damage the structure of the data model. Question 58 If you have two queries that have different data, but you want to concatenate the results into one query with all the combined rows. Which operation should you perform? Append Merge Combine columns Question 59 You’re creating a Power BI report with data from an Azure Analysis Services Cube. When the data refreshes in the cube, you would like to see it immediately in the Power BI report. How should you connect? Import Connect Live Direct query Question 60 Your company plans to completely separate development and production assets such as datasets, reports, and dashboards in Microsoft Power Bl. You need to recommend an application lifecycle strategy. The solution must minimize maintenance to update access and prevent end-users from viewing the development assets.What should you recommend? Create one workspace for development. From the workspace, publish an app for production Create separate workspaces for development and production. Grant the end users access to the production workspace. In the same workspace, create separate copies of the assets and append DEV to the names of the copied assets. Grant the end users access to the workspace Create production reports in a separate workspace that uses a shared dataset from the development workspace. Grant the end users access to the production workspace Question 61 You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions. You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions. What should you recommend? Split the model into multiple models Hide unused columns in the model Merge tables by using Power Query. Transpose Question 62 You have the following three versions of an Azure SQL database:* Test* Production* DevelopmentYou have a dataset that uses the development database as a data source. You need to configure the dataset so that you can easily change the data source between the development, test, and production database servers from powerbi.com.What will you do? Create a parameter and update the queries to use the parameter Create a query for each database server and hide the development tables. Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function. Create a JSON file that contains the database server names. Import the JSON file to the dataset Question 63 You need to create a visualization that compares revenue and cost over time. Which type of visualization should you use? waterfall chart stacked area chart line chart pi chart Question 64 You have worked hard to create a clean dataset of all your company’s customer details, including address and geocode. The Senior Director in your division has asked that all people within your company are aware of the high-quality, authoritative customer dataset that meets all the company’s standards.How can you achieve this goal? Select Promote content Ask authorized reviewers to Certify your content Select Featured content on home Create a new workspace with the high-quality data and add the entire company as members Time's up Trần Quân PL-300 Exam – Practice 7 PL-300 Exam – Practice 9