Microsoft Power BI Data Analyst Certification Practice Test 10

Select 1 option(s):

You need to use the correct Data Preview options to analyze the data.
Which Data Preview option should you use to find the below data quality issue?
“Find the number of distinct and unique rows for all columns”

Select 1 option(s):

• Sales has the details of sales made to customers and has a large number of records. Many new records are added daily.
• Customer is the list of customers. Customers have a few thousand records and there are additions and updates regularly.
• Territory is the geographical area for the sales teams. Territories are rarely updated.
• Date is a date table created with DAX.
You need to select the correct storage mode to meet your company’s requirements.
Which storage mode should you use for the following requirement?
“Create a calculated table using DAX”

Select 1 option(s):

What are two possible ways to achieve this goal? Each correct answer presents a complete solution.

Select 2 option(s):

You need to set up the dataflows to process and store data.
Which components should you use for the following process?
“Create dataflows using….”

Select 1 option(s):

You create a Power Bl report that shows sales for your organization.
You need to provide a version of the report that filters the data for a single country.
Solution:
1) Add a parameter to the dataset for country.
2) Add a query parameter specifying the parameter and country.
Does this solution meet the goal?

Select 1 option(s):

You create a Power Bl report that shows sales for your organization.
You need to provide a version of the report that filters the data for a single country.
Solution: Add a query string parameter specifying the table/field and country.
Does this solution meet the goal?

Select 1 option(s):

You need to find distinct and unique rows for each column in a dataset appearing in a single view, as shown below.

Which data quality option should you use?

Select 1 option(s):

Select 1 option(s):

You need to examine the quality of the data. The results of Data Preview are shown in the following images.

For the following statement, select Yes if the statement is true. Otherwise, select No.
“The DeviceType column can be used as a dimension”

Select 1 option(s):

You need to use the correct Data Preview options to analyze the data.
Which Data Preview option should you use to find the below data quality issue?
“Find the number of errors in a column.”

Select 1 option(s):

You need to combine these tables to create a new table. Which transformation should you use?

Select 1 option(s):

You need to identify issues affecting report performance.
Which two tools can you use to meet your goal? Each correct answer presents a complete solution.

Select 2 option(s):

You examine data quality in Power Query. You need to discover anomalies in the data.
Which option should you use?

Select 1 option(s):

• Sales has the details of sales made to customers and has a large number of records. Many new records are added daily.
• Customer is the list of customers. Customers have a few thousand records and there are additions and updates regularly.
• Territory is the geographical area for the sales teams. Territories are rarely updated.
• Date is a date table created with DAX.
You need to select the correct storage mode to meet your company’s requirements.
Which storage mode should you use for the following requirement?
“Display visualizations contain current Sales data”

Select 1 option(s):

There are errors, as shown in the below exhibit for the Rename Column step.

You need to resolve the errors without losing any data. How should you resolve the errors? To answer, select the appropriate options from the drop-down menus.

Select 1 option(s):

There are errors, as shown in the below exhibit for Cells.

You need to resolve the errors without losing any data. How should you resolve the errors? To answer, select the appropriate options from the drop-down menus.

Select 1 option(s):

Select 1 option(s):

You need to use the correct Data Preview options to analyze the data.
Which Data Preview option should you use to find the below data quality issue?
“Find the number of distinct, unique, and empty rows for a single column”

Select 1 option(s):

You need to examine the quality of the data. The results of Data Preview are shown in the following images.

For the following statement, select Yes if the statement is true. Otherwise, select No.
“A visualization containing the average amount of storage available for each DeviceType can be created using the Storage column.”

Select 1 option(s):

You need to examine the quality of the data. For each of the following statements, select Yes if the statement is true. Otherwise, select No.
1. You can use Data Preview to analyze the first 500 rows in the dataset by default;
2. You can use Data Preview to analyze the entire dataset;
3. You should enable Power Query background refresh to ensure that preview data is up to date

Select 1 option(s):

You use Power BI Desktop to create reports.
You need to create a new report on product data that is consistent with the standardized data.
What should you do?

Select 1 option(s):

You work for a company that uses Power Bl for financial reporting. Analysis and insights for sales are now required.
Existing Environment:
Power Bl service is used and workspaces have been created. Power Bl Desktop is installed.
Data Sources:
Sales data is stored in an Azure SQL database. Dimension data for Territory and Country is held in an Excel workbook in SharePoint Online.
Sales schema – The schema for the sales data is shown in the Schema exhibit. Product Ids are alphanumeric and consist of three letters followed by three numbers. The Sales amount is net of any taxes The Tax rate is 20 percent on all sales.
Territory workbook – The data for territories is shown in the Territory workbook exhibit.
Issues:
You observe the following issues:
The labels for the country are not consistent for territories.
The labels used are not the commonly used names in your company.
The labels do not follow naming corporate standards with capitalization, no special characters, and no abbreviations.
Users must be able to analyze sales by product type.
The tax amount on sales must be available in visualizations.
Sales managers are unable to create visualizations for sales grouped by Product type.
Sales managers must only be able to see sales for their territory.
Sales Schema

Territory Workbook

QUESTION
You need to make sure that the labels for the country are consistent. What should you do?

Select 1 option(s):

You import a set of data using Power Query. You need to identify whether a column contains only unique values.
Which two Data preview options can you use to meet your goal? Each correct answer presents a complete solution.

Select 2 option(s):

Which method should you use to combine the tables for the below scenario? Select the appropriate method.
Scenario: Multiple tables with a common key between them

Select 1 option(s):

Select 1 option(s):

Which method should you use to combine the tables for the below scenario? Select the appropriate method.
Scenario: Multiple tables with the same columns containing data for each month

Select 1 option(s):

Select 1 option(s):

You work for a company that uses Power Bl for financial reporting. Analysis and insights for sales are now required.
Existing Environment:
Power Bl service is used and workspaces have been created. Power Bl Desktop is installed.
Data Sources:
Sales data is stored in an Azure SQL database. Dimension data for Territory and Country is held in an Excel workbook in SharePoint Online.
Sales schema – The schema for the sales data is shown in the Schema exhibit. Product Ids are alphanumeric and consist of three letters followed by three numbers. The Sales amount is net of any taxes The Tax rate is 20 percent on all sales.
Territory workbook – The data for territories is shown in the Territory workbook exhibit.
Issues:
You observe the following issues:
The labels for country are not consistent for territories.
The labels used are not the commonly used names in your company.
The labels do not follow naming corporate standards with capitalization, no special characters, and no abbreviations.
Users must be able to analyze sales by product type.
The tax amount on sales must be available in visualizations.
Sales managers are unable to create visualizations for sales grouped by Product type.
Sales managers must only be able to see sales for their territory.
Sales Schema

Territory Workbook

QUESTION
You need to be able to show the amount of tax in visualizations.
You use Power Query.
Which three actions should you perform in sequence? Select the order in which the possible steps needs to be performed.
Possible steps:
1) Add a custom column named Tax to Sales
2) Pivot the Amount column.
3) Set the formula to be [Amount] / 5
4) Duplicate the Amount column.
5) Change the Data Type to Decimal number.

Select 1 option(s):

You create a calculated table named Country from the dataset for unique values of country in the table named Customer.
You generate visualizations for the sales data including showing sales by product manager. The product manager data is not in the data warehouse. The product manager data is in an Excel workbook on your local computer as shown in the Workbook exhibit below.

You do not have the Power BI On-Premises Gateway installed. You need to explain to the user when the data in the visualizations was last refreshed.
Visuals referring to the Sales table show the values from..

Select 1 option(s):

You need to replace the Excel workbook with a new version that contains updated data.
For the following statement(s), select Yes if the statement is true. Otherwise, select No.
“The Excel workbook can be edited in Power BI”

Select 1 option(s):

You need to replace the Excel workbook with a new version that contains updated data.
For the following statement(s), select Yes if the statement is true. Otherwise, select No.
“The Excel workbook must have the same file path and name as the original workbook.”

Select 1 option(s):

You need to create a measure for the average profit for products for use in visualizations.
You need to ensure that the measure you create will behave correctly when used with CALCULATE functions.
How should you create the measure? To answer, select the appropriate measure.

Select 1 option(s):

Users complain of performance issues when using visuals on the Power Bl report.
You need to optimize the report. What should you do?

Select 1 option(s):

A group of employees can be assigned to a single region.
You configure row-level security (RLS) for a Power Bl report so regional managers can only see the data of assigned employees. You need to fix the relationship between the Employee and Region tables so RLS can work.
Which two actions should you perform? Each correct answer presents part of the solution.

Select 2 option(s):

You need to improve the model’s performance.
Solution: Summarize the data & create aggregations.
Does this solution meet the goal?

Select 1 option(s):

There are relationships with other tables on the PO Number, PO Date, and Vendor columns.
You need to optimize model performance.
Which two actions should you perform? Each correct answer presents part of the solution.

Select 2 option(s):

You need to configure the Sales table and its columns for users to successfully use the Q&A feature in Dashboards.
Solution: Manage aggregations on the Sales table. Does this solution meet the goal?

Select 1 option(s):

You plan to create a visual that shows monthly sales to compare with the previous month’s sales. You need to create a measure to determine the previous month’s sales.
How should you create the measure?

Select 1 option(s):

You need to define which rows are available when creating a role.
What should you create?

Select 1 option(s):

Transactions

Calendar
The Transactions table has a column named Sales that contains the sales value of each transaction. The Sales column summarization property is set to Don’t summarize.
You need to calculate year-to-date sales using a quick measure.
How should you complete the measure as per the below quick measure setup fields?

Quick Measure

Select 1 option(s):

You need to create visualizations that display the sales for each quarter and the percent change from the same quarter in the previous year.
Which DAX function should you use to create the measure?

Select 1 option(s):

You plan to create a visual that shows the trend over time of inventory levels for both parts and finished products on the last production day each month.
You need to create a measure to determine the inventory level for the visual. Which two DAX functions can you use to meet the requirements? Each correct answer presents a complete solution.

Select 2 option(s):

A Date table has two relationships to the orders table for the two date fields. The relationship on the OrderDate column is the active relationship.
You need to create visualizations that display the total price of orders for each quarter based on both the OrderDate and the ShippingDate columns.
How should you create the measure for sales by shipping date?

Select 1 option(s):

You need to create a common date for a data model using DAX.
How should you complete the DAX expression? To answer, select the appropriate option.

Select 1 option(s):

There is a relationship between the Customer table and the Territory table using the TerritorylD column. The Territory table contains unique values for TerritorylD.
You need to be able to create visualizations showing sales by territory and filter by sales person and sales manager. How should you configure the relationship?
To answer, select the appropriate two options which represent a part of the solution.

Select 2 option(s):

Company1 Pharmaceuticals develops, manufactures, and supplies healthcare products. Company1 has annual reporting requirements including a balance sheet, income statement, and a profit and loss report for the previous year with projections tor the current year. The reporting within the financial system is limited and does not meet the Company’s requirements. Historically, producing these statements and reports has taken significant manual effort that was error-prone, and it has not been possible to perform useful analysis on financial data.
Planned Changes:
Power BI service and Power BI Desktop will be used to generate financial reports, statements, and analysis tor financial data.
Data Sources:
The finance system holds all financial data including sales and expenses. The finance system holds the chart of accounts. The finance system stores its data in an on-premises SQL Server. Sales prediction and budget plans are stored in Excel workbooks in SharePoint Online.
Reporting Requirements:
The following information should be produced:
> Income trend
> Expenditure trend
> Comparison of income and expenditure for the current year with the previous year
> Comparison of planned income and expenditure for next year with the current year.
Users should also be able to:
> Drill down on data by country and territory.
> Drill down using the chart of accounts structure.
> Use the Q&A feature of Power BI.
Security Requirements:
C level executives should have access to all analysis. Country and Territory managers should only have access to data for their country/territory.
Chart of Accounts:

Expenditure:

Income:

Predicted Sales:

Budget:

Data Model:

Question:
You are planning to configure the relationship between the Chart of Accounts and Predicted Sales to meet the security requirements. You need to be able to apply the security filter in both directions. What should you do?

Select 1 option(s):

You enable the Q&A feature in Power Bl dashboards.
Users are unable to use Q&A effectively because it does not understand terms your organization uses. You review the terms used in Q&A. The top five terms are:
• Hot
• Work order
• SKU
• Rating
• Overload
You need to teach the Q&A the top five terms that it does not recognize. You also need to be able to use Q&A with aggregations such as sum of and average of the work orders and revenue.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
1. You can use the Teach Q&A functionality on this dataset;
2. You can teach Q&A the above five terms;
3. You must create measures to be able to use the aggregations.

Select 1 option(s):

You need to configure the Sales table and its columns for users to successfully use the Q&A feature in Dashboards.
Solution: Add Synonyms to the Sales table. Does this solution meet the goal?

Select 1 option(s):

You need to create a Product dimension using the Sales table ProductName column.
How should you complete the DAX expression to generate the Product table? To answer, select the appropriate option.

Select 1 option(s):

You need to improve the model’s performance.
Solution: Remove columns from the data model not used in reports.
Does this solution meet the goal?

Select 1 option(s):

There are multiple management levels in your organization. You have an Employee table with the columns shown in the below exhibits.

Employee Data

Employee Table
There is a self-join, one-to-many relationship on this table between Managerld and Employeeld.
You need to be able to see all levels of the management hierarchy and be able to create visualizations that show data at each level of the hierarchy.
Which three actions should you perform in sequence?

Select 1 option(s):

Company1 Pharmaceuticals develops, manufactures, and supplies healthcare products. Company1 has annual reporting requirements including a balance sheet, income statement, and a profit and loss report for the previous year with projections tor the current year. The reporting within the financial system is limited and does not meet the Company’s requirements. Historically, producing these statements and reports has taken significant manual effort that was error-prone, and it has not been possible to perform useful analysis on financial data.
Planned Changes:
Power BI service and Power BI Desktop will be used to generate financial reports, statements, and analysis tor financial data.
Data Sources:
The finance system holds all financial data including sales and expenses. The finance system holds the chart of accounts. The finance system stores its data in an on-premises SQL Server. Sales prediction and budget plans are stored in Excel workbooks in SharePoint Online.
Reporting Requirements:
The following information should be produced:
> Income trend
> Expenditure trend
> Comparison of income and expenditure for the current year with the previous year
> Comparison of planned income and expenditure for next year with the current year.
Users should also be able to:
> Drill down on data by country and territory.
> Drill down using the chart of accounts structure.
> Use the Q&A feature of Power BI.
Security Requirements:
C level executives should have access to all analysis. Country and Territory managers should only have access to data for their country/territory.
Chart of Accounts:

Expenditure:

Income:

Predicted Sales:

Budget:

Data Model:

Question:
You plan to create visualizations for the income sheet and show the data changes by year for country and territory.
You need to transform the income table to enable these visualizations.
Are the four actions mentioned below should be performed in sequence to achieve the goal? Select Yes if true, otherwise select No.
1. Use the First Row as Headers transformation.
2. Select the year columns and Unpivot the columns
3. Rename the fourth column as Year and the fifth column as Sales Income.
4. Create relationships to the Chart of Accounts, Territory, and Year entities.

Select 1 option(s):

You need to configure the Sales table and its columns for users to successfully use the Q&A feature in Dashboards.
Solution: Set the row label on the Sales table. Does this solution meet the goal?

Select 1 option(s):

You need to optimize the report’s performance.
Which two actions should you perform? Each correct answer presents part of the solution.

Select 2 option(s):

Users report performance issues when using visuals on the Power Bl report.
You need to identify which visuals are taking the longest to process.
Which five actions should you perform in sequence? To answer, select the appropriate actions in the correct order.

Select 1 option(s):

Company1 Pharmaceuticals develops, manufactures, and supplies healthcare products. Company1 has annual reporting requirements including a balance sheet, income statement, and a profit and loss report for the previous year with projections tor the current year. The reporting within the financial system is limited and does not meet the Company’s requirements. Historically, producing these statements and reports has taken significant manual effort that was error-prone, and it has not been possible to perform useful analysis on financial data.
Planned Changes:
Power BI service and Power BI Desktop will be used to generate financial reports, statements, and analysis tor financial data.
Data Sources:
The finance system holds all financial data including sales and expenses. The finance system holds the chart of accounts. The finance system stores its data in an on-premises SQL Server. Sales prediction and budget plans are stored in Excel workbooks in SharePoint Online.
Reporting Requirements:
The following information should be produced:
> Income trend
> Expenditure trend
> Comparison of income and expenditure for the current year with the previous year
> Comparison of planned income and expenditure for next year with the current year.
Users should also be able to:
> Drill down on data by country and territory.
> Drill down using the chart of accounts structure.
> Use the Q&A feature of Power BI.
Security Requirements:
C level executives should have access to all analysis. Country and Territory managers should only have access to data for their country/territory.
Chart of Accounts:

Expenditure:

Income:

Predicted Sales:

Budget:

Data Model:

Question:
A visualization is created that shows the year, department, and forecast sales as shown in the below exhibit.

Which relationship should you configure, and how should you configure that relationship to solve the data problem?
1. The relationship to configure..
2. How to configure the relationship..
To answer, select the appropriate options.

Select 1 option(s):

Company1 Pharmaceuticals develops, manufactures, and supplies healthcare products. Company1 has annual reporting requirements including a balance sheet, income statement, and a profit and loss report for the previous year with projections tor the current year. The reporting within the financial system is limited and does not meet the Company’s requirements. Historically, producing these statements and reports has taken significant manual effort that was error-prone, and it has not been possible to perform useful analysis on financial data.
Planned Changes:
Power BI service and Power BI Desktop will be used to generate financial reports, statements, and analysis tor financial data.
Data Sources:
The finance system holds all financial data including sales and expenses. The finance system holds the chart of accounts. The finance system stores its data in an on-premises SQL Server. Sales prediction and budget plans are stored in Excel workbooks in SharePoint Online.
Reporting Requirements:
The following information should be produced:
> Income trend
> Expenditure trend
> Comparison of income and expenditure for the current year with the previous year
> Comparison of planned income and expenditure for next year with the current year.
Users should also be able to:
> Drill down on data by country and territory.
> Drill down using the chart of accounts structure.
> Use the Q&A feature of Power BI.
Security Requirements:
C level executives should have access to all analysis. Country and Territory managers should only have access to data for their country/territory.
Chart of Accounts:

Expenditure:

Income:

Predicted Sales:

Budget:

Data Model:

Question:
You need to create a table containing unique countries, and a table of unique combinations of countries and territories.
Which DAX expressions should you use? To answer, select the appropriate options from the drop-down menus.

Select 1 option(s):

You need to improve the model’s performance.
Solution: Hide the columns in the data model.
Does this solution meet the goal?

Select 1 option(s):

The report uses a measure named [Sales YOY % Growth] to calculate year over year sales growth. The report also has a measure named [Sales] that calculates sales for a selected period. The measure definitions are as follows:
Sales = SUM(Transactions [Sales])
Sales YOY % Growth = DIVIDE([Sales] – CALCULATE([Sales], SAMEPERIODLASTYEAR(‘Calendar'[Date])), CALCULATE([Sales], SAMEPERIODLASTYEAR(‘Calendar'[Date])))
You need to re-write the [Sales YOY % Growth] measure to improve report performance. To answer select the appropriate option of DAX measure.

Select 1 option(s):