Question 11
You merge data from Sales.Region, Region_Manager, Sales_Manager, and Manager into a single table named Region. What should you do next to meet the reporting requirements of the executives?
Question 12
You have a Rawer Bl report That imports a dace 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 three dates at the same time.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables. You create active relationships between the sales table and each date table.
Does this meet the goal?
* Due Date
* Order Date
* Delivery Date
You need to support the analysis of sales over time based on all three dates at the same time.
Solution: From the Fields pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and Delivery Date as calculated tables. You create active relationships between the sales table and each date table.
Does this meet the goal?
Question 13
You have the Power Bl data model shown in the following exhibit.
You need to create a measure to count the number of product categories that had products sold during a selected period. How should you complete the DAX expression? To answer, select the appropriate options in the answer are a. NOTE: Each correct selection is worth one point.
You need to create a measure to count the number of product categories that had products sold during a selected period. How should you complete the DAX expression? To answer, select the appropriate options in the answer are a. NOTE: Each correct selection is worth one point.
Question 14
Case Study 4 - Contoso Ltd 2
Overview
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four business units.
Existing Environment
Data and Sources
Data for the reports comes from the sources shown in the following table.
The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.
Balance Sheet Data
The balance sheet data is imported and includes the final monthly balances of each account in the format shown in the following table.
The balance sheet data always includes a row for each account for each month.
Product Catalog Data
The product catalog shows how products roll up to product categories, which roll up to the business units. The product list is provided in the format shown in the following table.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.
Business Issues
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an Excel formula. On occasion, there are conflicting results in the reports because the products and departments that roll up to each business unit are not defined consistently.
Requirements
Planned Changes
Contoso plans to automate and standardize the quarterly reporting process by using Power BI.
The company wants to reduce how long it takes to populate the reports to less than two days.
The company wants to create common logic for the business units, products, and departments.
The logic will be used across all reports, including but not limited to the quarterly reporting for the board.
Technical Requirements
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.
Security Requirements
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
- Analysts must be able to access all balance sheet and product catalog data.
- Analysts must be able to access only the profit and loss data of their respective business unit.
- Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the reports built by the analysts must NOT be included in the quarterly reports for the board.
- Analysts must NOT be able to share the quarterly reports with anyone.
- Analysts must NOT be able to make new reports by using the balance sheet data.
Report Requirements
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
The definitions and attributes for the products, departments, and business units must be consistent across all the reports.
The board must be able to get the following information from the quarterly reports:
- Revenue trends over time
- The ending balances of each account
- Changes in long-term liabilities from the previous quarter
- The percent of total revenue contributed by each product category
- A comparison of quarterly revenue versus the same quarter from the previous year
- The reports must be updated with the latest data by 5 AM each day.
You need to ensure that the data is updated to meet the report requirements. The solution must minimize configuration effort.
What should you do?
Overview
Contoso, Ltd. is a manufacturing company that produces sports equipment. Contoso holds quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including balance sheets and profit and loss statements for each of the company's four business units.
Existing Environment
Data and Sources
Data for the reports comes from the sources shown in the following table.
The balance sheet data is unrelated to the profit and loss results other than they both relate to dates.
Balance Sheet Data
The balance sheet data is imported and includes the final monthly balances of each account in the format shown in the following table.
The balance sheet data always includes a row for each account for each month.
Product Catalog Data
The product catalog shows how products roll up to product categories, which roll up to the business units. The product list is provided in the format shown in the following table.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.
Business Issues
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an Excel formula. On occasion, there are conflicting results in the reports because the products and departments that roll up to each business unit are not defined consistently.
Requirements
Planned Changes
Contoso plans to automate and standardize the quarterly reporting process by using Power BI.
The company wants to reduce how long it takes to populate the reports to less than two days.
The company wants to create common logic for the business units, products, and departments.
The logic will be used across all reports, including but not limited to the quarterly reporting for the board.
Technical Requirements
Contoso wants the reports and datasets refreshed with minimum manual effort.
The company wants to provide the board with a single package of reports that will contain custom navigation and links to supplementary information.
Maintenance, including manually updating data and access, must be minimized as much as possible.
Security Requirements
The reports must be made available to the board from powerbi.com. An Azure Active Directory (Azure AD) group will be used to share information with the board.
Contoso identifies the following security requirements for analyst access:
- Analysts must be able to access all balance sheet and product catalog data.
- Analysts must be able to access only the profit and loss data of their respective business unit.
- Analysts must be able to create new reports from the dataset that contains the profit and loss data, but the reports built by the analysts must NOT be included in the quarterly reports for the board.
- Analysts must NOT be able to share the quarterly reports with anyone.
- Analysts must NOT be able to make new reports by using the balance sheet data.
Report Requirements
You plan to relate the balance sheet table to a date table in Power BI in a many-to-one relationship based on the last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the ending balances for the quarter, as well as for the previous quarter.
The date table will contain the columns shown in the following table.
The definitions and attributes for the products, departments, and business units must be consistent across all the reports.
The board must be able to get the following information from the quarterly reports:
- Revenue trends over time
- The ending balances of each account
- Changes in long-term liabilities from the previous quarter
- The percent of total revenue contributed by each product category
- A comparison of quarterly revenue versus the same quarter from the previous year
- The reports must be updated with the latest data by 5 AM each day.
You need to ensure that the data is updated to meet the report requirements. The solution must minimize configuration effort.
What should you do?
Question 15
You merge data from Sales.Region, Region_Manager, Sales_Manager, and Manager into a single table named Region. What should you do next to meet the reporting requirements of the executives?