WorksheetsPowerBi - Part II
Total questions: 56
Worksheet time: 30mins
You create a Power BI dataset that contains the table shown in the following exhibit. You need to make the table available as an organizational data type in Microsoft Excel.
How should you configure the properties of the table? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Cost Center/ID/Yes
Headcount/Name/No
ID/ Headcount/ Yes
Name/ Cost center/No
You have the Power BI model shown in the following exhibit. A manager can represent only a single country.
You need to use row-level security (RLS) to meet the following requirements:
The managers must only see the data of their respective country.
The number of RLS roles must be minimized.
Which two actions should you perform? Each correct answer presents a complete solution.
Create a single role that filters Country[Manager_Email] by using the USERNAME DAX function.
Create a single role that filters Country[Manager_Email] by using the USEROBJECTID DAX function.
Create one role for each country.
For the relationship between Purchase Detail and Purchase, select Apply security filter in both directions.
For the relationship between Purchase and Purchase Detail, change the Cross filter direction to Single.
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.
Cardinality/denormalized table
Assume Referential Integrity/Star schema
Cardinality/snowflake schema
Cross filter direction/ denormalized table
You have a Power BI model that contains a table named Sales 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? To answer, select the appropriate options in the answer area.
EEVALUATE/SAMEPERIODLASTYEAR/'Date' [Date]
SUMX/DATESSMTD/[Date]
CALCULATE/PARALLELPERIOD/'DATE' [Month]
SUM/TOTALMTD/'Date' [Date]
You plan to create a report that will display sales data from the last year for multiple regions.
You need to restrict access to individual rows of the data on a per region-basis by using roles.
Which four 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.
Publish the report/ Add a filter to the report/From the Manage roles window, select Create / Add a filter to the report
Assign Users to the role/From the Manage roles window, select Create / Add a filter to the report/From the Manage roles window, select Create
From the Manage roles window, select Create/ Add a filter to the report/From the Manage roles window, select Create /Publish the report
Import data into your Power BI Desktop/From the Manage roles window, select Create/Assign Users to the role/Publish the report
Report developers and users provide feedback that the data model is too complex. The model contains the following tables.
The model has the following relationships:
There is a one-to-one relationship between Sales_Region and Region_Manager.
There are more records in Manager than in Region_Manager, but every record in Region_Manager has a corresponding record in Manager.
There are more records in Sales_Manager than in Sales_Region, but every record in Sales_Region has a corresponding record in Sales_Manager.
You need to denormalize the model into a single table. Only managers who are associated to a sales region must be included in the 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.
Merge [Sales_region] and [Region_Manager] by using a right join as new query named [Sales_region_and_Region_Manager]
Merge [Sales_Region] and [Region_Manager] by using inner join.
Merge [Region_Manager] and [Manager] by using inner join.
Merge [Sales_Region] and [Sales_Manager] by using an inner join.
Merge [Region_Manager] and [Manager] by using inner join.
Merge [Sales_region] and [Region_Manager] by using a right join as new query named [Sales_region_and_Region_Manager]
Merge [Region_Manager] and [Manager] by using inner join.
Merge [Sales_Region] and [Region_Manager] by using inner join.
Merge [Sales_Managers] and [Sales_Region] by using a left join
Merge [Sales_Region] and [Region_Manager] by using inner join.
Merge [Sales_Managers] and [Sales_Region] by using a left join
Merge [Sales_region] and [Region_Manager] by using a right join as new query named [Sales_region_and_Region_Manager]
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in 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 08:00 and 17:00.
The report is a single page that contains 15 AppSource 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?
Change any DAX measures to use iterator functions.
Enable visual interactions.
Replace the default visuals with AppSource visuals.
Split the visuals onto multiple pages.
You are creating a Microsoft Power BI imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories.
You import a fact table named Sales as shown in the exhibit. (Click the Exhibit tab.) The related dimension tables are imported into the model.
Sales contains the data shown in the following table. You are evaluating how to optimize the model.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
Yes/No/No
Yes/Yes/No
No/Yes/No
No/No/Yes
You have a Microsoft Power BI data model that contains three tables named Orders, Date, and City. There is a one-to-many relationship between Date and
Orders and between City and Orders.
The model contains two row-level security (RLS) roles named Role1 and Role2. Role1 contains the following filter.
City[State Province] = "Kentucky" Role2 contains the following filter. Date[Calendar Year] = 2020
If a user is a member of both Role1 and Role2, what data will they see in a report that uses the model?
The user will see data for which the State Province value is Kentucky or where the Calendar Year is 2020.
The user will receive an error and will not be able to see the data in the report.
The user will only see data for which the State Province value is Kentucky.
The user will only see data for which the State Province value is Kentucky and the Calendar Year is 2020.
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 are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: From Power Query Editor, you import the table and then add a filter step to the query.
Does this meet the goal?
Yes
No
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 are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You write a DAX expression that uses the FILTER function. Does this meet the goal?
Yes
No
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 are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, you need to import a sample of the data from the Order table.
Solution: You add a WHERE clause to the SQL statement. Does this meet the goal?
Yes
No
You are preparing a financial report in Power BI.
You connect to the data stored in a Microsoft Excel spreadsheet by using Power Query Editor as shown in the following exhibit.
You need to prepare the data to support the following:
Visualizations that include all measures in the data over time
Year-over-year calculations for all the measures
Which four 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. Select and Place:
Use headers as the first row
Rename the Attribute column as Year
Transpose the table
Use the first row as headers
Transpose the table
Use the first row as headers
Unpivot all the columns other than Measure
Rename the Measure column as Year
Rename the Measure column as Year
Use headers as the first row
Transpose the table
Unpivot all the columns other than Measure
Unpivot all the columns other than Measure
Use headers as the first row
Transpose the table
Rename the Measure column as Year
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 employee_id and employee_photo. You need to optimize the data model.
What should you configure for employee_id and employee_photo? To answer, select the appropriate options in the answer area.
Hide/ Delete
Change Type/ Sort
Sort / Hide
Delete/ Delete
You plan to create Power BI dataset to analyze attendance at a school. Data will come from two separate views named View1 and View2 in an Azure SQL database.
View1 contains the columns shown in the following table. View2 contains the columns shown in the following table. The views can be related based on the Class ID column.
Class ID is the unique identifier for the specified class, period, teacher, and school year. For example, the same class can be taught by the same teacher during two different periods, but the class will have a different class ID.
You need to design a star schema data model by using the data in both views. The solution must facilitate the following analysis:
The count of classes that occur by period
The count of students in attendance by period by day
The average number of students attending a class each month
In which table should you include the Teacher First Name and Period Number fields? To answer, select the appropriate options in the answer area.
Teacher fact/Attendance fact
Class dimension/ Teacher fact
Teacher fact/ Class dimension
Attendance fact/ Teacher fact
You have the Power BI model shown in the following exhibit. There are four departments in the Departments table.
You need to ensure that users can see the data of their respective department only. What should you do?
Create a slicer that filters Departments based on DepartmentID.
Create a row-level security (RLS) role for each department, and then define the membership of the role.
Create a DepartmentID parameter to filter the Departments table.
To the ConfidentialData table, add a calculated measure that uses the CURRENTGROUP DAX function.
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?
Create an inactive relationship between the tables and select Apply security filter in both directions.
Create an active relationship between the tables and select Apply security filter in both directions.
Create an inactive relationship between the tables and select Assume referential integrity.
Create an active relationship between the tables and select Assume referential integrity.
You have a column named UnitsInStock as shown in the following 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.
1 row / maintain
51 rows/ increase
75 rows/ reduce
0 rows/ maintain
You have a Power BI report.
You have the following tables.
You have the following DAX measure. Accounts :=
CALCULATE (DISTINCTCOUNT (Balances[AccountID]), LASTDATE ('Date'[Date])
Yes/No/No
No/Yes/Yes
Yes/No/Yes
No/No/Yes
You have the tables shown in the following table.
The Impressions table contains approximately 30 million records per month. You need to create an ad analytics system to meet the following requirements:
Present ad impression counts for the day, campaign, and site_name. The analytics for the last year are required.
Minimize the data model size.
Which two actions should you perform? Each correct answer presents part of the solution.
Create one-to-many relationships between the tables.
Group the Impressions query in Power Query by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.
Create a calculated table that contains Ad_id, Site_name, and Impression_date.
Create a calculated measure that aggregates by using the COUNTROWS function.
You are creating a Microsoft Power BI data model that has the tables shown in the following table.
The Products table is related to the ProductCategory table through the ProductCategoryID column. 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? To answer, select the appropriate options in the answer area.
One-to-one/Single
One-to-many/Single
One-to-one/ Both
Many-to-many/Single
You import a Power BI dataset that contains the following tables:
Date
Product
Product Inventory
The Product Inventory table contains 25 million rows. A sample of the data is shown in the following table.
The Product Inventory table relates to the Date table by using the DateKey column. The Product Inventory table relates to the Product table by using the
ProductKey column.
You need to reduce the size of the data model without losing information. What should you do?
Change Summarization for DateKey to Don't Summarize.
Remove the relationship between Date and Product Inventory
Change the data type of UnitCost to Integer.
Remove MovementDate.
You are enhancing a Power BI model that has DAX calculations.
You need to create a measure that returns the year-to-date total sales from the same date of the previous calendar year.
Which DAX functions should you use? To answer, select the appropriate options in the answer area.
CALCULATE/SUM/SAMEPERIODLASTYEAR
SUM/SAMEPERIODLASTYEAR/SUM
SAMEPERIODLASTYEAR/CALCULATE/DATESBETWEEN
DATESBETWEEN/CALCULATE/SUM
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 are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table named Order that has more than 100 million records.
During the development process, 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?
Yes
No
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: For each date foreign key, you add inactive relationships between the sales table and the date table.
Does this meet the goal?
Yes
No
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 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?
Yes
No
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.
Does this meet the goal?
Yes
No
You receive revenue data that must be included in Microsoft Power BI reports.
You preview the data from a Microsoft Excel source in Power Query as shown in the following exhibit.
You plan to create several visuals from the data, including a visual that shows revenue split by year and product.
You need to transform the data to ensure that you can build the visuals. The solution must ensure that the columns are named appropriately for the data that they contain.
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.
Select Department and Product and Unpivot Other Columns
Select Use First Row as Header
Rename the Attribute column to Year and the value column to Revenue
Select Department and Product and Unpivot Other Columns
Rename the Attribute column to Year and the value column to Revenue
Select Use First Row as Header
Select Use First Row as Header
Select Use First Row as First Row
Rename the Attribute column to Year and the value column to Revenue
Select Department and Product and Unpivot Columns
Rename the Attribute column to Revenue and the value column to Year
Select Use First Row as First Row
You have a Power BI report named Orders that supports the following analysis:
Total sales over time
The count of orders over time
New and repeat customer counts
The data model size is nearing the limit for a dataset in shared capacity. The model view for the dataset is shown in the following exhibit.
The data view for the Orders table is shown in the following exhibit.
The Orders table relates to the Customers table by using the CustomerID column. The Orders table relates to the Date table by using the OrderDate column.
For each of the following statements, select Yes if the statement is true, Otherwise, select No.
Yes/No/Yes
No/No/Yes
Yes/Yes/Yes
No/Yes/No
You are building a financial report by using Power BI.
You have a table named financials that contains a column named Date and a column named Sales.
You need to create a measure that calculates the relative change in sales as compared to the previous quarter.
How should you complete the measure? To answer, select the appropriate options in the answer area.
DATEADD/FILTER/DIVIDE
FIND/DATEADD/CALCULATE
CALCULATE/DATEADD/DIVIDE
DIVIDE/FILTER/FIND
You are creating a Power BI model and report.
You have a single table in a data model named Product. Product contains the following fields:
ID
Name
Color
Category
Total Sales
You need to create a calculated table that shows only the top eight products based on the highest value in Total Sales.
How should you complete the DAX expression? To answer, drag the appropriate values to the correct targets. Each value 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.
TOPN/DESC
DESC/MAXX
MAXX/ASC
RELATEDTABLE/ASC
You are creating a sales report in Power BI for the NorthWest region sales territory of your company. Data will come from a view in a Microsoft SQL Server database. A sample of the data is shown in the following table:
The report will facilitate the following analysis:
The count of orders and the sum of total sales by Order Date
The count of customers who placed an order
The average quantity per order
You need to reduce data refresh times and report query times.
Which two actions should you perform? Each correct answer presents part of the solution.
Set the data type for SalesOrderNumber to Decimal Number.
Remove the CustomerKey and ProductKey columns.
Remove the TaxAmt and Freight columns.
Filter the data to only the NorthWest region sales territory.
You are creating a Power BI model that contains a table named Store. Store contains the following fields.
You plan to create a map visual that will show store locations and provide the ability to drill down from Country to State/Province to City.
What should you do to ensure that the locations are mapped properly?
Change the data type of City, State/Province, and Country.
Set Summarization for City, State/Province, and Country to Don't summarize.
Set the data category of City, State/Province, and Country.
Create a calculated column that concatenates the values in City, State/Province, and Country.
You are building a data model for a Power BI report. You have data formatted as shown in the following table.
You need to create a clustered bar chart as shown in the following exhibit. What should you do?
From Power Query Editor, split the Machine-User column by using a delimiter.
From Power Query Editor, create a column that contains the last three digits of the Machine-User column.
In a DAX function, create two calculated columns named Machine and User by using the SUBSTITUTE function.
In a DAX function, create two measures named Machine and User by using the SUBSTITUTE function.
You need create a date table in Power BI that must contain 10 full calendar years, including the current year.
How should you complete the DAX expression? To answer, select the appropriate options in the answer area.
DATE/YEAR/CALENDAR
TODAY/YEAR/DATE
YEAR/TODAY/CALENDAR
CALENDAR/DATE/TODAY
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: You create measures that use the USERELATIONSHIP DAX function to filter sales on the active relationship between the sales table and the date table.
Does this meet the goal?
Yes
No
You have a Power BI report that contains a measure named Total Sales.
You need to create a new measure that will return the sum of Total Sales for a year up to a selected date.
How should you complete the DAX expression? To answer, select the appropriate options in the answer area.
CALCULATE/EOMONTH('Date'[Date])
TOTALYTD/'Date'[Date]
SUM/TODAY()
EVALUATE/LASTDATE('Date'[Date])
You have a Power BI report for the procurement department. The report contains data from the following tables.
There is a one-to-many relationship from Suppliers to LineItems that uses the ID and Supplier ID columns.
The report contains the visuals shown in the following table.
You need to minimize the size of the dataset without affecting the visuals. What should you do?
Merge Suppliers and LineItems.
Remove the LineItems[Description] column.
Remove the rows from LineItems where LineItems[Invoice Date] is before the beginning of last month.
Group LineItems by LineItems[Invoice ID] and LineItems[Invoice Date] with a sum of LineItems[Price].
You have a Power BI report for the marketing department. The report reports on web traffic to a blog and contains data from the following tables.
There is a one-to-many relationship from Posts to Traffic that uses the URL and URL Visited columns.
The report contains the visuals shown in the following table. The dataset takes a long time to refresh.
You need to modify Posts and Traffic queries to reduce load times.
Which two actions will reduce the load times? Each correct answer presents part of the solution.
Remove the rows in Posts in which Posts[Publish Date] is in the last seven days.
Remove the rows in Traffic in which Traffic[URL Visited] does not contain ג€blogג€.
Remove Traffic[IP Address], Traffic[Browser Agent], and Traffic[Referring URL].
Remove Posts[Full Text] and Posts[Summary].
Remove the rows in Traffic in which Traffic[Referring URL] does not start with ג€/ג€.
You are creating a quick measure as shown in the following exhibit.
You need to create a monthly rolling average measure for Sales over time.
How should you configure the quick measure calculation? To answer, select the appropriate options in the answer area.
Total Sales/ Month/ Years
Month/ Year/ Quarters
Total Sales/ Date/ Months
Total Order Qty/ Total Sales/ Quarters
You have the Power BI data model shown in the following exhibit.
The Sales table contains records of sales by day from the last five years up until today’s date.
You plan to create a measure to return the total sales of March 2021 when March 2022 is selected.
Which DAX expression should you use?
Calculate (Sum(Sales[Sales]), PREVIOUSYEAR( dimDate[Date])
TOTALYTD (SUM(Sales[Sales]), dimDate[Date] )
Calculate (SUM(Sales[Sales]), SAMEPERIODLASTYEAR(dimDate[Date] ))
SUM(Sales[Sales])
You use Power BI Desktop to load data from a Microsoft SQL Server database. While waiting for the data to load, you receive the following error.
You need to resolve the error.
What are two ways to achieve the goal? Each correct answer presents a complete solution.
Reduce the number of rows and columns returned by each query.
Split log running queries into subsets of columns and use Power Query to merge the queries.
Use Power Query to combine log running queries into one query
Disable query folding on long running queries.
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.
From Power Query Editor, you profile the data shown in the following exhibit. The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You split the IoT DateTime column into a column named Date and a column named Time.
Does this meet the goal?
Yes
No
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.
From Power Query Editor, you profile the data shown in the following exhibit. The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You remove the IoT GUID column and retain the IoT ID column. Does this meet the goal?
Yes
No
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.
From Power Query Editor, you profile the data shown in the following exhibit. The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You change the IoT DateTime column to the Date data type. Does this meet the goal?
Yes
No
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in 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 08:00 and 17:00.
The report is a single page that contains 15 AppSource 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?
Change any DAX measures to use iterator functions.
Remove unused columns from tables in the data model.
Replace the default visuals with AppSource visuals.
Increase the number of times that the dataset is refreshed.
You have a Power BI data model that contains two tables named Products and Sales. A one-to-many relationship exists between the tables.
You have a report that contains a report-level filter for Products.
You need to create a measure that will return the percent of total sales for each product. The measure must respect the report-level filter when calculating the total.
How should you complete the DAX measure? To answer, drag the appropriate DAX functions to the correct targets. Each function 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.
ALL/FILTER
CALCULATE/FILTER
ALLSELECTED/FILTER
FILTER/SELECTEDVALUE
You have a Power BI data model that analyzes product sales over time. The data model contains the following tables.
A one-to-many relationship exists between the tables. The auto date/time option for the data model is enabled.
You need to reduce the size of the data model while maintaining the ability to analyze product sales by month and quarter.
Which two actions should you perform? Each correct answer presents part of the solution.
Create a relationship between the Date table and the Sales table.
Disable the auto date/time option.
Create a Date table and select Mark as Date Table.
Disable the load on the Date table
Remove the relationship between the Product table and the Sales table.
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in 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 08:00 and 17:00.
The report is a single page that contains 15 AppSource 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?
Implement row-level security (RLS).
Remove unused columns from tables in the data model.
Replace the default visuals with AppSource visuals.
Enable visual interactions.
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: 2018-12-31 at 08: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?
Apply a transformation to extract the first 11 characters of the logged column.
Add a conditional column that outputs 2018 if the Logged column starts with 2018 and set the data type of the new column to Whole Number.
Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.
Apply a transformation to extract the last 11 characters of the Logged column and set the data type of the new column to Date.
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.
From Power Query Editor, you profile the data shown in the following exhibit.
The IoT GUID and IoT ID columns are unique to each row in the query.
You need to analyze IoT events by the hour and day of the year. The solution must improve dataset performance.
Solution: You create a custom column that concatenates the IoT GUID column and the IoT ID column and then delete the IoT GUID and IoT ID columns.
Does this meet the goal?
Yes
No
You have a Power BI model that contains a table named Employee. The table contains the following data.
Each employee has one manager as shown in the ParentEmployeeID column. All reporting paths lead to the CEO at the top of the organizational hierarchy.
You need to create a calculated column that returns the count of levels from each employee to the CEO.
Which DAX expression should you use?
PATHLENGTH(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]))
PATHITEM(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]),1,INTEGER)
PATHCONTAINS(PATH(Employee[EmployeeID],Employee[ParentEmployeeID]),1)
PATH(Employee[EmployeeID],Employee[ParentEmployeeID])
You have a Microsoft Power BI report. The size of PBIX file is 550 MB. The report is accessed by using an App workspace in 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 08:00 and 17:00.
The report is a single page that contains 15 AppSource 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?
Replace the default visuals with AppSource visuals.
Remove unused columns from tables in the data model.
Change the imported dataset to DirectQuery
Increase the number of times that the dataset is refreshed.
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?
In the Total Item Quantity column, replace the null values with 0.
In the Tax Amount column, remove rows that contain null values.
In the Freight Amount column, remove rows that contain null values.
In the Freight Amount column, replace the null values with 0.
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?
DISTINCTCOUNT(‘Employees’[Salary])
CALCULATE(DISTINCTCOUNT(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
CALCULATE(AVERAGE(‘Employees’[Salary]), ‘Employees’[Department] = “Sales”)
AVERAGE(‘Employees’[Salary])
You have the Power BI data model shown in the following exhibit. The Sales table has the following columns.
The data model must support the following analysis:
Total sales by product by month in which the order was placed
Quantities sold by product by day on which the order was placed
Number of sales transactions by quarter in which the order was placed
Yes/No/Yes
Yes/Yes/No
No/No/Yes
Yes/Yes/Yes
