Topic 1, Litware, Inc. Case Study
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
Overview
Litware, Inc. is an online retailer that uses Microsoft Power Bl dashboards and reports.
The company plans to leverage data from Microsoft SQL Server databases, Microsoft Excel files, text files, and several other data sources.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
Existing Environment
Sales Data
Litware has online sales data that has the SQL schema shown in the following table.
In the Date table, the dateid column has a format of yyyymmdd and the month column has a format of yyyymm.
The week column in the Date table and the weekid column in the Weekly_Returns table have a format of yyyyww.
The regionid column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
• Executives require a visual that shows sales by region.
• Regional managers require a visual to analyze weekly sales and returns.
• Sales managers must be able to see the sales data of their respective region only.
• The sales managers require a visual to analyze sales performance versus sales targets.
• The sale department requires reports that contain the number of sales transactions.
• Users must be able to see the month in reports as shown in the following example: Feb 2020.
• The customer service department requires a visual that can be filtered by both sales month and ship month independently.
You need to address the data concerns before creating the data model.
What should you do in Power Query Editor?
- A . Select Column distribution.
- B . Select the sales_amount column and apply a number filter.
- C . Select Column profile, and then select the sales_amount column.
- D . Transform the sales_amount column to replace negative values with 0.
You need to create a calculated column to display the month based on the reporting requirements.
Which DAX expression should you use?
- A . FORMAT(‘Date'[date], "MMM YYYY")
- B . FORMAT(‘Date’ [date], "M YY")
- C . FORMAT(‘Date'[date_id], "MMM") & "" & FORMAT(‘Date'[year], "#")
- D . FORMAT(‘Date’ [date_id], "MMM YYYY")
You need to create the required relationship for the executive’s visual.
What should you do before you can create the relationship?
- A . Change the data type of Sales[region_id] to Whole Number.
- B . In the Sales table, add a measure for sum(sales_amount).
- C . Change the data type of sales[sales_id] to Text.
- D . Change the data type of sales [region_id] to Decimal Number.
A
Explanation:
Scenario: Executives require a visual that shows sales by region.
Need to change the sales_id column from Varchar to Whole Number (Integer).
What should you create to meet the reporting requirements of the sales department?
- A . a measure that uses a formula of SUM (Sales [sales_id])
- B . a calculated column that use a formula of COUNTA (sales [sales_id])
- C . a measure that uses a formula of COUNTROWS (Sales)
- D . a calculated column that uses a formula of SUM (Sales [sales_id])
C
Explanation:
The sale department requires reports that contain the number of sales transactions.
The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.
Reference: https://docs.microsoft.com/en-us/dax/countrows-function-dax
You need to create a relationship between the Weekly_Returns table and the Date table to meet the reporting requirements of the regional managers.
What should you do?
- A . In the Weekly.Returns table, create a new calculated column named date-id in a format of yyyymmdd and use the calculated column to create a relationship to the Date table.
- B . Add the Weekly_Returns data to the Sales table by using related DAX functions.
- C . Create a new table based on the Date table where date-id is unique, and then create a many-to-many relationship to Weekly_Return.
A
Explanation:
Scenario: Region managers require a visual to analyze weekly sales and returns.
To relate the two tables we need a common column.
HOTSPOT
You need to create a visualization to meet the reporting requirements of the sales managers.
How should you create the visualization? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Scenario: The sales managers require a visual to analyze sales performance versus sales targets.
Box 1: KPI
A Key Performance Indicator (KPI) is a visual cue that communicates the amount of progress made toward a measurable goal.
Box 2: Sales[sales_amount]
Box 3: Date[month]
Time > FiscalMonth. This value will represent the trend.
Box 4: Targets[sales_target]
Reference: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-kpi
You need to provide a solution to provide the sales managers with the required access.
What should you include in the solution?
- A . Create a security role that has a table filter on the Sales_Manager table where username = UserName()
- B . Create a security role that has a table filter on the Region_Manager table where sales_manager_id = UserPrincipalName().
- C . Create a security role that has a table filter on the Sales_Manager table where name = UserName().
- D . Create a security role that has a table filter on the Sales_Manager table where username = sales_manager_id.
A
Explanation:
https://powerbi.microsoft.com/en-us/blog/using-username-in-dax-with-row-level-security/
You need to create relationships to meet the reporting requirements of the customer service department.
What should you create?
- A . an additional date table named ShipDate, a one-to-many relationship from Sales[sales_date_id] to Date[date_id], and a one-to-many relationship from Sales[sales_ship_date_id] to ShipDate[date_id]
- B . an additional date table named ShipDate, a many-to-many relationship from Sales[sales_date_id] to Date[date_id], and a many-to-many relationship from Sales[sales_ship_date_id] to ShipDate[date_id]
- C . a one-to-many relationship from Date[date_id] to Sales[sales_date_id] and another one-to-many relationship from Date[date_id] to Weekly_Returns[week_id]
- D . a one-to-many relationship from Sales[sales_date_id] to Date[date_id] and a one-to-many relationship from Sales[sales_ship_date_id] to Date[date_id]
- E . a one-to-many relationship from Date[date_id] to Sales[sales_date_id] and another one-to-many relationship from Date[date_id] to Sales[sales_ship_date_id]
A
Explanation:
Scenario: The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
HOTSPOT
You publish the dataset to powerbi.com.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
You need to get data from the Microsoft SQL Server tables.
What should you use to configure the connection’
- A . import that uses a Microsoft account
- B . DirectQuery that uses the end-user s credentials
- C . DirectQuery that uses a database credential
- D . Import that uses a database credential
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?
- A . Apply row-level security (RLS) to the Region table based on the sales manager username.
- B . Configure a bi-directional relationship between Region and Sales.Region.
- C . Create a DAX calculated column that retrieves the region manager from the Weekly.Returns table based on the sales.regionjd column.
- D . In the Region table, create a hierarchy that has the manager name, and then the sales manager name.
HOTSPOT
You publish the semantic model to powerbi.com.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
Topic 2, Contoso Ltd, Case Study
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
Existing Environment
Contoso, Ltd. is a manufacturing company that produces outdoor equipment Contoso has quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including profit and loss statements for each of the company’s four business units, a company balance sheet, and net income projections for the next quarter.
Data and Sources
Data for the reports comes from three sources. Detailed revenue, cost and expense data comes from an Azure SQL database. Summary balance sheet data comes from Microsoft Dynamics 365 Business Central. The balance sheet data is not related to the profit and loss results, other than they both relate to dates.
Monthly revenue and expense projections for the next quarter come from a Microsoft SharePoint Online list. Quarterly projections relate to the profit and loss results by using the following shared dimensions: date, business unit, department, and product category.
Net Income Projection Data
Net income projection data is stored in a SharePoint Online list named Projections in the format shown in the following table.
Revenue projections are set at the monthly level and summed to show projections for the quarter.
Balance Sheet Data
The balance sheet data is imported with final balances for each account per month in the format shown in the following table.
There is always a row for each account for each month in the balance sheet data.
Dynamics 365 Business Central Data
Business Central contains a product catalog that shows how products roll up to product categories, which roll up to business units. 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.
Planned Changes
Contoso plans to automate and standardize the quarterly reporting process by using Microsoft Power Bl. The company wants to how long it takes to populate reports to less than two days. The company wants to create common logic for business units, products, and departments to 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 minimal manual effort
The company wants to provide a single package of reports to the board that contains 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. A mail-enabled security group will be used to share information with the board.
The analysts responsible for each business unit must see all the data the board sees, except the profit and loss data, which must be restricted to only their business unit’s data. The analysts must be able to build new reports from the dataset that contains the profit and loss data, but any reports that the analysts build must not be included in the quarterly reports for the board. The analysts must not be able to share the quarterly reports with anyone.
Report Requirements
You plan to relate the balance sheet to a standard date table in Power Bl 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.
Projections must contain a column named RevenueProjection that contains the revenue projection amounts.
A relationship must be created from Projections to a table named Date that contains the columns shown in the following table.
The relationships between products and departments to business units must be consistent across all reports.
The board must be able to get the following information from the quarterly reports:
• Revenue trends over time
• Ending balances for each account
• A comparison of expenses versus projections by quarter
• Changes in long-term liabilities from the previous quarter
• A comparison of quarterly revenue versus the same quarter during the prior year
DRAG DROP
You need to create a DAX measure in the data model that only allows users to see projections at the appropriate levels of granularity.
How should you complete the measure? 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. NOTE: Each correct selection is worth one point.
Explanation:
Scenario: Revenue projections are set at the monthly level and summed to show projections for the quarter.
Box 1: IF
Box 2: ISFILTERED
ISFILTERED returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE.
Box 3: SUM
Reference: https://docs.microsoft.com/en-us/dax/isfiltered-function-dax
HOTSPOT
You need to calculate the last day of the month in the balance sheet data to ensure that you can relate the balance sheet data to the Date table.
Which type of calculation and which formula should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: A DAX Calculated measure
Box 2: Date.EndofQuarter(#date([Year],[Mont],1))
ENDOFQUARTER returns the last date of the quarter in the current context for the specified column of dates.
The following sample formula creates a measure that returns the end of the quarter, for the current context.
= ENDOFQUARTER(DateTime[DateKey])
Reference: https://docs.microsoft.com/en-us/dax/endofquarter-function-dax
HOTSPOT
You need to grant access to the business unit analysts.
What should you configure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: The Viewer role to the workspace
The Viewer role gives a read-only experience to its users. They can view dashboards, reports, or workbooks in the workspace, but can’t browse the datasets or dataflows. Use the Viewer role wherever you would previously use a classic workspace set to “Members can only view Power BI content”.
Box 2: Build
The analysts must be able to build new reports from the dataset that contains the profit and loss data.
Scenario: The reports must be made available to the board from powerbi.com.
The analysts responsible for each business unit must see all the data the board sees, except the profit and loss data, which must be restricted to only their business unit’s data. The analysts must be able to build new reports from the dataset that contains the profit and loss data, but any reports that the analysts build must not be included in the quarterly reports for the board. The analysts must not be able to share the quarterly reports with anyone.
Reference: https://www.nickyvv.com/2019/08/the-new-power-bi-workspace-viewer-role-explained.html
You need to recommend a strategy to consistently define the business unit, department, and product category data and make the data usable across reports.
What should you recommend?
- A . Create a shared dataset for each standardized entity.
- B . Create dataflows for the standardized data and make the dataflows available for use in all imported datasets.
- C . For every report, create and use a single shared dataset that contains the standardized data.
- D . For the three entities, create exports of the data from the Power Bl model to Excel and store the data in Microsoft OneDrive for others to use as a source.
DRAG DROP
Once the profit and loss dataset is created, which four actions should you perform in sequence to ensure that the business unit analysts see the appropriate profit and loss data? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Explanation:
https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls
https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-build-permissions
What is the minimum number of datasets and storage modes required to support the reports?
- A . two imported datasets
- B . a single DirectQuery dataset
- C . two DirectQuery datasets
- D . a single imported dataset
D
Explanation:
"The analysts responsible for each business unit must see all the data the board sees, except the profit and loss data, which must be restricted to only their business unit’s data. The analysts must be able to build new reports from the dataset that contains the profit and loss data" => one dataset and two separate workspaces Reason: All data can be imported into one dataset also if these are two logical models. Shared dimensions can be reconsumed in both models. Reports and additional materials can be shared to the board with an app. The "profit and loss" data model needs RLS for the analysts and the analysts must have just read access to the original workspace. In a separate workspace with contributer (or more rights) they can create new reports (with live connection to the dataset). It is also stated that the new reports mustn’t be shared so therefore no need to include them into the app. Import vs. DirectQuery: Due to RLS requirements an imported dataset is needed. It is not possible with file sources and Sharepoint lists.
Which DAX expression should you use to get the ending balances in the balance sheet reports?
- A . CALCULATE (
SUM( BalanceSheet [BalanceAmount] ),
DATESQTD( ‘Date'[Date] )
) - B . CALCULATE (
SUM( BalanceSheet [BalanceAmount] ),
LASTDATE( ‘Date'[Date] )
) - C . FIRSTNONBLANK ( ‘Date’ [Date]
SUM( BalanceSheet[BalanceAmount] )
) - D . CALCULATE (
MAX( BalanceSheet[BalanceAmount] ),
LASTDATE( ‘Date’ [Date] )
)
A
Explanation:
Scenario: 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.
DATESQTD returns a table that contains a column of the dates for the quarter to date, in the current context.
Reference: https://docs.microsoft.com/en-us/dax/datesqtd-function-dax
Which two types of visualizations can be used in the balance sheet reports to meet the reporting goals? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
- A . a line chart that shows balances by quarter filtered to account categories that are long-term liabilities.
- B . a clustered column chart that shows balances by date (x-axis) and account category (legend) without
filters. - C . a clustered column chart that shows balances by quarter filtered to account categories that are long-term liabilities.
- D . a pie chart that shows balances by account category without filters.
- E . a ribbon chart that shows balances by quarter and accounts in the legend.
AE
Explanation:
https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-types-for-reports-and-q-and-a
HOTSPOT
How should you distribute the reports to the board? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Using a workspace membership
Scenario:
The company wants to provide a single package of reports to the board that contains custom navigation and links to supplementary information.
Note: Workspace is a shared environment for a group of people. You can have multiple Power BI content in a workspace. One workspace can have hundreds of dashboards, reports, and datasets in it.
Box 2: A mail-enabled security group
Scenario: Security Requirements
The reports must be made available to the board from powerbi.com. A mail-enabled security group will be used to share information with the board.
You need to ensure that the data is updated to meet the report requirements. The solution must minimize configuration effort.
What should you do?
- A . From each report in powerbi.com. select Refresh visuals
- B . From Power Bl Desktop, download the PBIX file and refresh the data.
- C . Configure a scheduled refresh without using an on-premises data gateway.
- D . Configure a scheduled refresh by using an on-premises data gateway
You need to create the relationship between the product list and the revenue results. The solution must minimize the time it takes to render visuals.
What should you set as the relationship cardinality?
- A . One to one
- B . Many to one
- C . One to many
- D . Many to many
Topic 3, Northwind Traders
Case study
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all question included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other question on this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study
To display the first question on this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
Overview. General Overview
Northwind Traders is a specialty food import company.
The company recently implemented Power BI to better understand its top customers, products, and suppliers.
Overview. Business Issues
The sales department relies on the IT department to generate reports in Microsoft SQL Server Reporting Services (SSRS). The IT department takes too long to generate the reports and often misunderstands the report requirements.
Existing Environment. Data Sources
Northwind Traders uses the data sources shown in the following table.
Source2 is exported daily from a third-party system and stored in Microsoft SharePoint Online.
Existing Environment. Customer Worksheet
Source2 contains a single worksheet named Customer Details.
The first 11 rows of the worksheet are shown in the following table.
All the fields in Source2 are mandatory.
The Address column in Customer Details is the billing address, which can differ from the shipping address.
Existing Environment. Azure SQL Database
Source1 contains the following table:
– Orders
– Products
– Suppliers
– Categories
– Order Details
– Sales Employees
The Orders table contains the following columns.
The Order Details table contains the following columns.
The address in the Orders table is the shipping address, which can differ from the billing address.
The Products table contains the following columns.
The Categories table contains the following columns.
The Suppliers table contains the following columns.
The Sales Employees table contains the following columns.
Each employee in the Sales Employees table is assigned to one sales region. Multiple employees can be assigned to each region.
Requirements. Report Requirements
Northwind Traders requires the following reports:
– Top Products
– Top Customers
– On-Time Shipping
The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order month or quarter, product category, and sales region.
The Top Products report will show the top 20 products based on the highest sales amounts sold in a selected order month or quarter, sales region, and product category. The report must also show which suppliers provide the top products.
The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:
– The percentage of orders that were shipped late by country and shipping region
– Customers that had multiple late shipments during the last quarter
Northwind Traders defines late orders as those shipped after the required shipping date.
The warehouse shipping department must be notified if the percentage of late orders within the current month exceeds 5%.
The reports must show historical data for the current calendar year and the last three calendar years.
Requirements. Technical Requirements
Northwind Traders identifies the following technical requirements:
– A single dataset must support all three reports.
– The reports must be stored in a single Power BI workspace.
– Report data must be current as of 7 AM Pacific Time each day.
– The reports must provide fast response times when users interact with a visualization.
– The data model must minimize the size of the dataset as much as possible, while meeting the report requirements and the technical requirements.
Requirements. Security Requirements
Access to the reports must be granted to Azure Active Directory (Azure AD) security groups only.
An Azure AD security group exists for each department.
The sales department must be able to perform the following tasks in Power BI:
– Create, edit, and delete content in the reports.
– Manage permissions for workspaces, datasets, and report.
– Publish, unpublish, update, and change the permissions for an app.
– Assign Azure AD groups role-based access to the reports workspace.
Users in the sales department must be able to access only the data of the sales region to which they are assigned in the Sales Employees table.
Power BI has the following row-level security (RLS) Table filter DAX expression for the Sales Employees table.
[EmailAddress] = USERNAME()
RLS will be applied only to the sales department users. Users in all other departments must be able to view all the data.
You need to design the data model to meet the report requirements.
What should you do in Power BI Desktop?
- A . From Power Query, use a DAX expression to add columns to the Orders table to calculate the calendar quarter of the OrderDate column, the calendar month of the OrderDate column, the calendar quarter of the ShippedDate column, and the calendar month of the ShippedDate column.
- B . From Power Query, add columns to the Orders table to calculate the calendar quarter and the calendar month of the OrderDate column.
- C . From Power Bl Desktop, use the Auto date/time option when creating the reports.
- D . From Power Query, add a date table. Create an active relationship to the OrderDate column in the Orders table and an inactive relationship to the ShippedDate column in the Orders table.
B
Explanation:
Use Power Query to calculate calendar quarter and calendar month.
Scenario:
✑ A single dataset must support all three reports:
– The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order month or quarter, product category, and sales region.
– The Top Products report will show the top 20 products based on the highest sales amounts sold in a selected order month or quarter, sales region, and product category.
✑ The data model must minimize the size of the dataset as much as possible, while meeting the report requirements and the technical requirements.
HOTSPOT
You need to create a measure that will return the percentage of late orders.
How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: CALCULATE
CALCULATE evaluates an expression in a modified filter context.
Syntax: CALCULATE(<expression>[, <filter1> [, <filter2> [, …]]])
Expression – The expression to be evaluated.
filter1, filter2,… (Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.
Box 2: FILTER
FILTER returns a table that represents a subset of another table or expression.
Syntax: FILTER(<table>, <filter>)
Table- The table to be filtered. The table can also be an expression that results in a table.
Filter – A Boolean expression that is to be evaluated for each row of the table. For example, [Amount]
> 0 or [Region] = "France"
Box 3: Orders [ShippedDate]> Orders [RequiredDate]
Northwind Traders defines late orders as those shipped after the required shipping date.
Reference:
https://docs.microsoft.com/en-us/dax/calculate-function-dax
https://docs.microsoft.com/en-us/dax/filter-function-dax
HOTSPOT
You need to create a relationship in the dataset for RLS.
What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: many-to-one
Each employee in the Sales Employees table is assigned to one sales region. Multiple employees can be assigned to each region.
The Suppliers table has a Region column.
Box 2: Suppliers table
You need to create the On-Time Shipping report. The report must include a visualization that shows the percentage of late orders.
Which type of visualization should you create?
- A . bar chart
- B . scatterplot
- C . pie chart
A
Explanation:
Scenario: The On-Time Shipping report will show the following metrics for a selected shipping month or quarter:
The percentage of orders that were shipped late by country and shipping region Customers that had multiple late shipments during the last quarter
Note: Bar and column charts are some of the most widely used visualization charts in Power BI. They can be used for one or multiple categories. Both these chart types represent data with rectangular bars, where the size of the bar is proportional to the magnitude of data values.
The difference between the two is that if the rectangles are stacked horizontally, it is called a bar chart. If the rectangles are vertically aligned, it is called a column chart.
Reference: https://www.pluralsight.com/guides/bar-and-column-charts-in-power-bi
HOTSPOT
You need to create the Top Customers report.
Which type of filter should you use, and at which level should you apply the filter? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Top N
Scenario: The Top Customers report will show the top 20 customers based on the highest sales amounts in a selected order month or quarter, product category, and sales region.
Once you drag to SKU to Visual level filter you should get Top N option
Note: The two most common filter types: automatic and manual.
Then there are more advanced filters.
Box 2: Visual
Once you drag to SKU to Visual level filter you should get Top N option.
Reference: https://powerbidocs.com/2020/01/21/power-bi-top-n-filters/
You need to minimize me size of the dataset. The solution must meet the report requirements.
What should you do?
- A . Change lite OrderlD column in the Orders table to the text data type.
- B . Filter out discontinued products while importing the Product table.
- C . Remove the QuantityPerUnit column from the Products table
- D . Group the Categories table by the CategorylD column.
You need to configure access for the sales department users. The solution must me meet the security requirements.
What should you do?
- A . Add the sales department as a member of the reports workspace
- B . Add the Azure Active Directory group of the sales department as an Admin of the reports workspace.
- C . Distribute an app to the users in the Azure Active Directory group of the sales department.
- D . Share each report to the Azure Active Directory group of the sales department.
HOTSPOT
You need to design the data model and the relationships for the Customer Details worksheet and the Orders table by using Power BI. The solution must meet the report requirements.
For each of the following statement, select Yes if the statement is true, Otherwise, select No. NOTE: Each correct selection is worth one point.
HOTSPOT
You need to create a solution to meet the notification requirements of the warehouse shipping department.
What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct select is worth one point:
You need to create the dataset.
Which dataset mode should you use?
- A . DirectQuery
- B . Import
- C . Live connection
- D . Composite
D
Explanation:
Composite Model means now you can have a model, that very large tables of that are coming from the DirectQuery connection, without the need for importing, and small tables to be imported to be accessible quickly.
Topic 4, Misc. Questions
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 Bl. 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?
- A . Yes
- B . No
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 Bl. 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?
- A . Yes
- B . No
B
Explanation:
The filter is applied after the data is imported.
Instead add a WHERE clause to the SQL statement.
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial
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 Bl. 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?
- A . Yes
- B . No
B
Explanation:
The filter is applied after the data is imported.
Instead add a WHERE clause to the SQL statement.
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial
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 create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source.
You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition.
Solution: You add a Power Apps custom visual to the report.
Does this meet the goal?
- A . Yes
- B . No
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 create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source.
You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition.
Solution: In the Power Query M code, you replace references to the Excel file with DataSourceExcel. Does this meet the goal?
- A . Yes
- B . No
A
Explanation:
Instead modify the source step of the queries to use DataSourceExcel as the file path.
Note: Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns.
Reference: https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/
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 create a parameter named DataSourceExcel that holds the file name and location of a Microsoft Excel data source.
You need to update the query to reference the parameter instead of multiple hard-coded copies of the location within each query definition.
Solution: You create a new query that references DataSourceExcel.
Does this meet the goal?
- A . Yes
- B . No
B
Explanation:
Instead modify the source step of the queries to use DataSourceExcel as the file path.
Note: Parameterising a Data Source could be used in many different use cases. From connecting to different data sources defined in Query Parameters to load different combinations of columns.
Reference: https://www.biinsight.com/power-bi-desktop-query-parameters-part-1/
You have the visual shown in the Original exhibit. (Click the Original tab.)
You need to configure the visual as shown in the Modified exhibit. (Click the Modified tab.)
What should you add to the visual?
- A . a measure
- B . a trendline
- C . a forecast
- D . an Average line
C
Explanation:
Explore forecast results by adjusting the desired confidence interval or by adjusting outlier data to see how they affect results.
Reference: https://powerbi.microsoft.com/fr-fr/blog/introducing-new-forecasting-capabilities-in-power-view-for-office-365/
You plan to create the chart shown in the following exhibit.
How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?
- A . Create a horizontal line that has a fixed value of 24,000.
- B . Add a measure to the visual that uses the following DAX expression.
Measure – PERCENTUEX.EXC (Sales,Sales[Total Sales],©.40) - C . Add a new percentile line that uses Total Sales as the measure and 40% as the percentile.
- D . Add a measure to the visual that uses the following DAX expression.
Measure = PERCENTILEX.INC (Sales,Sales[Total Sales],6.40)
C
Explanation:
The analytics feature enables you to show percentiles across groups specified along a specific axis.
Example:
You plan to create the chart shown in the following exhibit.
How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?
- A . Create a horizontal line that has a fixed value of 24,000.
- B . Add a measure to the visual that uses the following DAX expression.
Measure – PERCENTUEX.EXC (Sales,Sales[Total Sales],©.40) - C . Add a new percentile line that uses Total Sales as the measure and 40% as the percentile.
- D . Add a measure to the visual that uses the following DAX expression.
Measure = PERCENTILEX.INC (Sales,Sales[Total Sales],6.40)
C
Explanation:
The analytics feature enables you to show percentiles across groups specified along a specific axis.
Example:
You plan to create the chart shown in the following exhibit.
How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?
- A . Create a horizontal line that has a fixed value of 24,000.
- B . Add a measure to the visual that uses the following DAX expression.
Measure – PERCENTUEX.EXC (Sales,Sales[Total Sales],©.40) - C . Add a new percentile line that uses Total Sales as the measure and 40% as the percentile.
- D . Add a measure to the visual that uses the following DAX expression.
Measure = PERCENTILEX.INC (Sales,Sales[Total Sales],6.40)
C
Explanation:
The analytics feature enables you to show percentiles across groups specified along a specific axis.
Example:
You plan to create the chart shown in the following exhibit.
How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?
- A . Create a horizontal line that has a fixed value of 24,000.
- B . Add a measure to the visual that uses the following DAX expression.
Measure – PERCENTUEX.EXC (Sales,Sales[Total Sales],©.40) - C . Add a new percentile line that uses Total Sales as the measure and 40% as the percentile.
- D . Add a measure to the visual that uses the following DAX expression.
Measure = PERCENTILEX.INC (Sales,Sales[Total Sales],6.40)
C
Explanation:
The analytics feature enables you to show percentiles across groups specified along a specific axis.
Example:
You plan to create the chart shown in the following exhibit.
How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?
- A . Create a horizontal line that has a fixed value of 24,000.
- B . Add a measure to the visual that uses the following DAX expression.
Measure – PERCENTUEX.EXC (Sales,Sales[Total Sales],©.40) - C . Add a new percentile line that uses Total Sales as the measure and 40% as the percentile.
- D . Add a measure to the visual that uses the following DAX expression.
Measure = PERCENTILEX.INC (Sales,Sales[Total Sales],6.40)
C
Explanation:
The analytics feature enables you to show percentiles across groups specified along a specific axis.
Example:
You have a table that contains sales data and approximately 1,000 rows.
You need to identify outliers in the table.
Which type of visualization should you use?
- A . area chart
- B . donut chart
- C . scatter plot
- D . pie chart
C
Explanation:
Outliers are those data points that lie outside the overall pattern of distribution & the easiest way to detect outliers is though graphs. Box plots, Scatter plots can help detect them easily.
Reference: https://towardsdatascience.com/this-article-is-about-identifying-outliers-through-funnel-plots-using-the-microsoft-power-bi-d7ad16ac9ccc
You have a collection of reports for the HR department of your company.
You need to create a visualization for the HR department that shows a historic employee counts and predicts trends during the next six months.
Which type of visualization should you use?
- A . scatter chart
- B . ribbon chart
- C . line chart
- D . key influences
C
Explanation:
The best data for forecasting is time series data or uniformly increasing whole numbers. The line chart has to have only one line.
Try forecasting: Try the new forecasting capabilities of Power View today on your own data or with the sample report available as part of the Power BI report samples. To view your own data, upload a workbook with a Power View time series line chart to Power BI for Office 365.
Reference: https://powerbi.microsoft.com/en-us/blog/introducing-new-forecasting-capabilities-in-power-view-for-office-365
You have the visual shown in the exhibit. (Click the Exhibit tab.)
You need to show the relationship between Total Cost and Total Sales over time.
What should you do?
- A . Add a play axis.
- B . Add a slicer for the year.
- C . From the Analytics pane, add an Average line.
- D . Create a DAX measure that calculates year-over-year growth.
A
Explanation:
You can set up a date field in play axis, and then scatter chart will animate how measure values are compared to each other in each point of a time.
Reference: https://radacad.com/storytelling-with-power-bi-scatter-chart
HOTSPOT
You are creating a column chart visualization.
You configure groups as shown in the Groups exhibit. {Click the Groups tab.)
The visualization appears as shown in the Chart exhibit. (Click the Chart tab.)
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
You build a report to help the sales team understand its performance and the drivers of sales. The team needs to have a single visualization to identify which factors affect success.
Which type of visualization should you use?
- A . Key influences
- B . Funnel chart
- C . Q&A
- D . Line and clustered column chart
A
Explanation:
The key influencers visual helps you understand the factors that drive a metric you’re interested in. It analyzes your data, ranks the factors that matter, and displays them as key influencers.
The key influencers visual is a great choice if you want to:
✑ See which factors affect the metric being analyzed.
✑ Contrast the relative importance of these factors.
For example, do short-term contracts have more impact on churn than long-term contracts?
Reference: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-influencers
HOTSPOT
You need to create a visual as shown in the following exhibit.
The indicator color for Total Sales will be based on % Growth to Last Year.
The solution must use the existing calculations only.
How should you configure the visual? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Background color
To format the Color column based on its field values, select Conditional formatting for the Color field,
and then select Background color or Font color.
In the Background color or Font color dialog box, select Field value from the Format by drop-down field.
Box 2: Field value
With conditional formatting for tables in Power BI Desktop, you can specify customized cell colors, including color gradients, based on field values.
Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting
HOTSPOT
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. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Total Sales
We select the field Total Sales
Box 2: Date
Select a date field.
Box 3: Month
Monthly periods.
Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-quick-measures
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?
- A . Merge tables by using Power Query.
- B . Hide unused columns in the model.
- C . Split the model into multiple models.
- D . Transpose.
A
Explanation:
Combining data means connecting to two or more data sources, shaping them as needed, then consolidating them into a useful query.
When you have one or more columns that you’d like to add to another query, you merge the queries.
Note: The RELATEDTABLE function is a shortcut for CALCULATETABLE function with no logical expression.
CALCULATETABLE evaluates a table expression in a modified filter context and returns A table of values.
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data
You have a sales system that contains the tables shown in the following table.
The Date table is marked as a date table.
DatelD is the date data type. You need to create an annual sales growth percentage measure.
Which DAX expression should you use?
- A . SUM(sales[sales_amount]) – CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR(‘Date'[DateID])
- B . (SUM(‘Sales’[sales_amount]) – CALCULATE(SUM(‘Sales’[sales_amount]), SAMEPERIODLASTYEAR(‘Date’[DateID])))
/ CALCULATE(SUM(‘Sales’[sales_amount]), SAMEPERIODLASTYEAR(‘Date’[DateID])) - C . CALCULATE(SUM(sales[sales_amount]), DATESYTD(‘Date’[DateID]))
- D . CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR(‘Date’[DateID]))
B
Explanation:
SAMEPERIODLASTYEAR returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
Reference: https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax
You build a report to analyze customer transactions from a database that contains the tables shown in the following table.
You import the tables.
Which relationship should you use to link the tables?
- A . one-to-many from Customer to Transaction
- B . one-to-one between Customer and Transaction
- C . one-to-many from Transaction to Customer
- D . many-to-many between Customer and Transaction
A
Explanation:
Each customer can have many transactions.
For each transaction there is exactly one customer.
HOTSPOT
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 employeejd and employee_photo.
You need to optimize the data model
What should you configure for employeejd and employee.photo? To answer, select the appropriate options in the answer area.
Explanation:
Box 1: Hide
Optimize data by hiding fields and sorting visualization data
Box 2: Delete
The fastest way to optimize your Power BI report is to limit the number of columns to only the ones you need in your data model. Go through your tables in Power Query and determine what fields are being used. Delete these columns if they are not being used in any of your reports or calculations.
Reference: https://tessellationtech.io/optimizing-power-bi-reports/
HOTSPOT
You are creating a Microsoft Power Bl model that has two tables named CityData and Sales.
CityData contains only the data shown in the following table.
Sales contains only the data shown in the following table.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Yes
The Related function returns a related value from another table.
The RELATED function requires that a relationship exists between the current table and the table with related information. You specify the column that contains the data that you want, and the function follows an existing many-to-one relationship to fetch the value from the specified column in the related table. If a relationship does not exist, you must create a relationship.
Box 2: Yes
Box 3: No
TX only occurs in the Sales table, but not in the CityData table.
Reference:
https://docs.microsoft.com/en-us/dax/related-function-dax
https://docs.microsoft.com/en-us/dax/calculate-function-dax
DRAG DROP
You build a report about warehouse inventory data. The dataset has more than 10 million product records from 200 warehouses worldwide.
You have a table named Products that contains the columns shown in the following table.
Warehouse managers report that it is difficult to use the report because the report uses only the product name in tables and visuals. The product name is contained within the ProductDescription column and is always the fourth value.
You need to modify the report to support the warehouse managers requirement to explore inventory levels at different levels of the product hierarchy. The solution must minimize the model size.
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.
Explanation:
HOTSPOT
You are creating a Microsoft Power Bl 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.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
Explanation:
Reference: https://finance-bi.com/power-bi-basket-analysis/
You are configuring a Microsoft Power Bl data model to enable users to ask natural language questions by using Q&A. You have a table named Customer that has the following measure.
Customer Count = DISTINCTCOUNT(Customer[CustomerID])
Users frequently refer to customers as subscribers.
You need to ensure that the users can get a useful result for "subscriber count" by using Q&A. The solution must minimize the size of the model.
What should you do?
- A . Add a description of "subscriber count" to the Customer Count measure.
- B . Set Summarize By to None for the CustomerlD column.
- C . Add a description of "Subscriber" to the Customer table.
- D . Add a synonym of "subscriber" to the Customer table.
B
Explanation:
You can add synonyms to tables and columns.
Note: This step applies specifically to Q&A (and not to Power BI reports in general). Users often have a variety of terms they use to refer to the same thing, such as total sales, net sales, total net sales. You can add these synonyms to tables and columns in the Power BI model.
This step applies specifically to Q&A (and not to Power BI reports in general). Users often have a variety of terms they use to refer to the same thing, such as total sales, net sales, total net sales. You can add these synonyms to tables and columns in the Power BI model.
Reference: https://docs.microsoft.com/en-us/power-bi/natural-language/q-and-a-best-practices
You have a Microsoft Power Bl 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 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?
- A . Split the visuals onto multiple pages.
- B . Implement row-level security (RLS).
- C . Replace the default visuals with custom visuals.
- D . Increase the number of times that the dataset is refreshed.
DRAG DROP
You have a Microsoft Power Bl workspace.
You need to grant the user capabilities shown in the following table.
The solution must use the principle of least privilege.
Which user role should you assign to each user? To answer, drag the appropriate roles to the correct users. Each role 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. NOTE: Each correct selection is worth one point.
Explanation:
User 1 = Member
User 2 = Contributor
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-new-workspaces
You have four sales regions. Each region has multiple sales managers.
You implement row-level security (RLS) in a data model. You assign the relevant distribution lists to each role.
You have sales reports that enable analysis by region. The sales managers can view the sales records of their region. The sales managers are prevented from viewing records from other regions.
A sales manager changes to a different region.
You need to ensure that the sales manager can see the correct sales data.
What should you do?
- A . From Microsoft Power Bl Desktop, edit the Row-Level Security setting for the reports.
- B . Change the Microsoft Power Bl license type of the sales manager.
- C . Manage the permissions of the underlying dataset
- D . Request that the sales manager be added to the correct Azure Active Directory group.
D
Explanation:
Using AD Security Groups, you no longer need to maintain a long list of users.
All that you will need to do is to put in the AD Security group with the required permissions and Power BI will do the REST! This means a small and simple security file with the permissions and AD Security group.
Note: Configure role mappings
Once published to Power BI, you must map members to dataset roles.
Members can be user accounts or security groups. Whenever possible, we recommend you map security groups to dataset roles. It involves managing security group memberships in Azure Active Directory. Possibly, it delegates the task to your network administrators.
Reference:
https://www.fourmoo.com/2018/02/20/dynamic-row-level-security-is-easy-with-active-directory-security-groups/
https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance
You have five sales regions. Each region is assigned a single salesperson.
You have an imported dataset that has a dynamic row-level security (RLS) role named Sales. The Sales role filters sales transaction data by salesperson.
Salespeople must see only the data from their region.
You publish the dataset to powerbi.com, set RLS role membership, and distribute the dataset and related reports to the salespeople.
A salesperson reports that she believes she should see more data.
You need to verify what data the salesperson currently sees.
What should you do?
- A . Use the Test as role option to view data as the salesperson’s user account.
- B . Use the Test as role option to view data as the Sales role.
- C . Instruct the salesperson to open the report in Microsoft Power Bl Desktop.
- D . Filter the data in the reports to match the intended logic in the filter on the sales transaction table.
A
Explanation:
Validate the roles within Power BI Desktop
After you’ve created your roles, test the results of the roles within Power BI Desktop.
From the Modeling tab, select View as.
The View as roles window appears, where you see the roles you’ve created.
Select a role you created, and then select OK to apply that role.
The report renders the data relevant for that role.
You can also select Other user and supply a given user.
Select OK.
The report renders based on what that user can see.
Reference: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls
You have a collection of reports for the HR department of your company. The datasets use row-level security (RLS). The company has multiple sales regions that each has an HR manager. You need to ensure that the HR managers can interact with the data from their region only. The HR managers must be prevented from changing the layout of the reports.
How should you provision access to the reports for the HR managers?
- A . Create a new workspace, copy the datasets and reports, and add the HR managers as members of the workspace.
- B . Publish the reports to a different workspace other than the one hosting the datasets.
- C . Publish the reports in an app and grant the HR managers access permission.
- D . Add the HR managers as members of the existing workspace that hosts the reports and the datasets.
C
Explanation:
Note: Row-level security (RLS) with Power BI can be used to restrict data access for given users. Filters restrict data access at the row level, and you can define filters within roles. In the Power BI service, members of a workspace have access to datasets in the workspace. RLS doesn’t restrict this data access.
Reference: https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls
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?
- A . 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.
- B . 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.
- C . Create separate workspaces for development and production. Grant the end users access to the production workspace.
- D . Create one workspace for development. From the workspace, publish an app for production.
HOTSPOT
You have a report page that contains the visuals 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. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: cross-filter
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
Box 2: cross-highlight
Example:
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
HOTSPOT
You have a report page that contains the visuals 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. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: cross-filter
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
Box 2: cross-highlight
Example:
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
HOTSPOT
You have a report page that contains the visuals 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. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: cross-filter
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
Box 2: cross-highlight
Example:
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
HOTSPOT
You have a report page that contains the visuals 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. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: cross-filter
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
Box 2: cross-highlight
Example:
By default, selecting a data point in one visual on a report page will cross-filter or cross-highlight the other visuals on the page.
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?
- A . Use default visuals or custom visuals uploaded from a .pbiviz file.
- B . Use only default visuals.
- C . Use default or any custom visuals from the marketplace.
- D . Use default or certified custom visuals.
You have a Microsoft Power Bl dashboard. The report used to create the dashboard uses an imported dataset from a Microsoft SQL Server data source.
The dashboard is shown in the exhibit. (Click the Exhibit tab.)
What occurred at 12:03:06 PM?
- A . A user pressed F5
- B . A new transaction was added to the data source.
- C . A user added a comment to a tile.
- D . The dashboard tile cache refreshed.
A
Explanation:
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data
You have a report that contains four pages. Each page contains slicers for the same four fields. Users report that when they select values on a slicer on one page, the visuals are not updated on all the pages. You need to recommend a solution to ensure that users can select a value once to filter the results on all the pages.
What are two possible recommendations to achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
- A . Sync the slicers across the pages.
- B . Replace the slicers with page-level filters.
- C . Replace the slicers with visual-level filters.
- D . Create a bookmark for each slicer value.
- E . Replace the slicers with report-level filters.
AE
Explanation:
Add a report-level filter to filter an entire report.
The visuals on the active page, and on all pages in the report, change to reflect the new filter.
You can sync a slicer and use it on any or all pages in a report.
You have a report that contains four pages. Each page contains slicers for the same four fields. Users report that when they select values on a slicer on one page, the visuals are not updated on all the pages. You need to recommend a solution to ensure that users can select a value once to filter the results on all the pages.
What are two possible recommendations to achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
- A . Sync the slicers across the pages.
- B . Replace the slicers with page-level filters.
- C . Replace the slicers with visual-level filters.
- D . Create a bookmark for each slicer value.
- E . Replace the slicers with report-level filters.
AE
Explanation:
Add a report-level filter to filter an entire report.
The visuals on the active page, and on all pages in the report, change to reflect the new filter.
You can sync a slicer and use it on any or all pages in a report.
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 Bl Desktop?
- A . the bookmark order
- B . the layer order
- C . the tab order
- D . the X position
C
Explanation:
If you find yourself unable to navigate to an object or visual while using a keyboard, it may be because the report author has decided to hide that object from the tab order. Report authors commonly hide decorative objects from the tab order. If you find that you cannot tab through a report in a logical manner, you should contact the report author. Report authors can set the tab order for objects and visuals.
Reference: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-accessibility-consuming-tools
You create a dashboard by using the Microsoft Power Bl Service. The dashboard contains a card visual that shows total sales from the current year. You grant users access to the dashboard by using the viewer role on the workspace. A user wants to receive daily notifications of the number shown on the card visual. You need to automate the notifications.
What should you do?
- A . Share the dashboard to the user.
- B . Create a subscription.
- C . Create a data alert.
- D . Tag the user in a comment.
C
Explanation:
You can subscribe yourself and your colleagues to the report pages, dashboards, and paginated reports that matter most to you. Power BI e-mail subscriptions allow you to:
Decide how often you want to receive the emails: daily, weekly, hourly, monthly, or once a day after the initial data refresh.
Choose the time you want to receive the email, if you choose daily, weekly, hourly, or monthly.
Note: Email subscriptions don’t support most custom visuals. The one exception is those custom visuals that have been certified.
Email subscriptions don’t support R-powered custom visuals at this time.
Incorrect Answers:
A: Set data alerts to notify you when data in your dashboards changes beyond limits you set.
Reference:
https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-report-subscribe
https://docs.microsoft.com/en-us/power-bi/create-reports/service-set-data-alerts
You have multiple dashboards.
You need to ensure that when users browse the available dashboards from powerbi.com. they can see which dashboards contain Personally Identifiable Information (Pll). The solution must minimize configuration effort and impact on the dashboard design.
What should you use?
- A . Active Directory groups
- B . tiles
- C . data classifications
- D . comments
You publish a report to a workspace named Customer Services. The report identifies customers that have potential data quality issues that must be investigated by the customer services department of your company.
You need to ensure that customer service managers can create task lists in Microsoft Excel based on the data.
Which report setting should you configure?
- A . Don’t allow end user to save filters on this report.
- B . Change default visual interaction from cross highlighting to cross filtering.
- C . Enable the updated filter pane, and show filters in the visual header for this report.
- D . Allow users to add comments to this report.
- E . Choose the type of data you allow your end users to export.
E
Explanation:
https://powerbi.microsoft.com/en-us/blog/announcing-persistent-filters-in-the-service/
You have a report that contains three pages. One of the pages contains a KPI visualization. You need to filter all the visualizations in the report except for the KPI visualization.
Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.
- A . Add the same slicer to each page and configure Sync slicers.
- B . Edit the interactions of the KPI visualization.
- C . Configure a page-level filter.
- D . Edit the interactions of the slicer that is on the same page as the KPI visualization.
- E . Configure a report-level filter.
AD
Explanation:
Slicers are another way of filtering. They narrow the portion of the dataset that is shown in the other report visualizations.
By default, slicers on report pages affect all the other visualizations on that page, including each other. Use visual interactions to exclude some page visualizations from being affected by others.
Reference: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-slicers
You have a Microsoft Power Bl dashboard.
You need to ensure that consumers of the dashboard can give you feedback that will be visible to the other consumers of the dashboard.
What should you use?
- A . Feedback
- B . Subscribe
- C . Comments
- D . Mark as favorite
C
Explanation:
https://docs.microsoft.com/en-us/power-bi/consumer/end-user-comment
HOTSPOT
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 scheduled refresh in powerbi.com.
What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Append Queries as New.
There are two primary ways of combining queries: merging and appending.
When you have one or more columns that you’d like to add to another query, you merge the queries.
When you have additional rows of data that you’d like to add to an existing query, you append the query.
Box 2: Disable loading the query to the data model
For every query that loads into model memory will be consumed. and Memory is our asset in the Model, less memory consumption leads to better performance in most of the cases. The best approach is to disable loading.
Reference:
https://docs.microsoft.com/en-us/power-query/append-queries
https://community.powerbi.com/t5/Power-Query/Append-vs-Append-as-new-for-performance/td-p/1822710
You have a Microsoft SharePoint Online site that contains several document libraries. One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure.
You need to load only the manufacturing reports to a table for analysis.
What should you do in Microsoft Power Bl Desktop?
- A . Get data from a SharePoint Online folder, enter the site URL, and then select Combine & Load.
- B . Get data from a SharePoint Online list and enter the site URL. Edit the query and filter by the path to the manufacturing reports library.
- C . Get data from a SharePoint Online folder and enter the site URL. Edit the query and filter by the path to the manufacturing reports library.
- D . Get data from a SharePoint Online list, enter the site URL, and then select Combine & Load.
B
Explanation:
We have to import Excel files from SharePoint, so we need the connector SharePoint folder which is used to get access to the files stored in the library. SharePoint list is a collection of content that has rows and columns (like a table) and is used for task lists, calendars, etc. Since we have to filter only on manufacturing reports, we have to select Transform and then filter by the corresponding folder path.
https://docs.microsoft.com/en-us/power-query/connectors/sharepointlist
HOTSPOT
You have a folder of monthly transaction extracts.
You plan to create a report to analyze the transaction data.
You receive the following email message: "Hi. I’ve put 24 files of monthly transaction data onto the shared drive. File Transactions201901.csv through Transactions201912.csv have the latest set of columns, but files Transactions201801.csv to Transactions201812.csv have an older layout without the extra fields needed for analysis. Each file contains 10 to 50 transactions."
You get data from the folder and select Combine & Load.
The Combine Files dialog box is shown in the exhibit. (Click the Exhibit tab.)
For each of the following statements, select Yes if the statement is true. Otherwise, select No. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Yes
The four columns used in the 2018 transactions are already displayed.
Box 2: Yes
The columns used are based on the entire dataset. The additional columns in the 2019 files will be detected.
Box 3: Yes
Note: Under the hoods, Power BI will automatically detect which delimiter to use, and may even promote the first row as headers. You can manually change the delimiter, or define how Power BI should handle data types. You can set it to automatically detect data types based on first 200 rows, or the entire dataset or you can even opt out the detection of data types.
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?
- A . 1318
- B . 1319
- C . 21318 Lasalle Street
- D . 21319 Lasalle Street
D
Explanation:
Example:
Replace the text "ur" with the text "or" in the table.
Reference: https://docs.microsoft.com/en-us/powerquery-m/table-replacevalue
DRAG DROP
You receive revenue data that must be included in Microsoft Power Bl reports.
You perform an initial load of the data from a Microsoft Excel source 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.
Explanation:
Step 1: Select Use Header as First Row.
Step 2: Select Department and Product and Unpivot Other Columns
Unpivot Other Columns: This command unpivots unselected columns. Use this command in a query when not all columns are known. New columns added during a refresh operation are also unpivoted.
Step 3: Rename the Attribute column to Year and the Value column to Revenue.
You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all similar values are in one column. This is necessary, for example, to create a chart or a report.
When you unpivot, you unpack the attribute-value pairs that represent an intersection point of the
new columns and re-orient them into flattened columns:
Values (in blue on the left) are unpivoted into a new column (in blue on the right).
Attributes (in green on the left) are unpivoted into a new column (in green on the right) and duplicates are correspondingly mapped to the new Values column.
Reference: https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f588221c7098
You import a large dataset to Power Query Editor.
You need to identify whether a column contains only unique values.
Which two Data Preview options can you use? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point
- A . Show whitespace
- B . Column distribution
- C . Column profile
- D . Column quality
- E . Monospaced
HOTSPOT
You view a query named Transactions as shown in the following exhibit.
The query gets CSV files from a folder.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: 9
9 distinct CSV files.
Box 2: 10
10 distinct dates.
https://pediaa.com/what-is-the-difference-between-unique-and-distinct-in-sql/#:~:text=Unique%20and%20Distinct%20are%20two%20SQL%20constraints.,the%20records%20fr om%20a%20table.
Your company has employees in 10 states.
The company recently decided to associate each state to one of the following three regions: East, West, and North.
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?
- A . Create a new aggregation that summarizes by employee.
- B . Create a new group on the state column and set the Group type to List.
- C . Create a new group on the state column and set the Group type to Bin.
- D . Create a new aggregation that summarizes by state.
B
Explanation:
https://www.mssqltips.com/sqlservertip/4720/binning-and-grouping-data-with-power-bi/
You have a query that returns the data shown in the following exhibit.
You need to configure the query to display the data as shown in the following exhibit.
Which step should you use in the query?
- A . =Table.ExpandListColum(Table.TransformColunins(Source, {{"classes". Splitter.SplitTextByDelimiter(”,”, QuoteStyle.None), let itemType – (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "classes")
- B . = Table.Unpivot(Source, {"classes"}, "Attribute", "Value")
- C . = Table.SplitColumn(Source, "classes". Splitter.SplitTextByDelimiterf",", QuoteStyle.None), {"classes.1"})
- D . = Table.SplitColumn(Source, "classes". Splitter.SplitTextByPositions({10}), {"classes.1"})
B
Explanation:
Power Query Unpivot columns: You might want to unpivot data, sometimes called flattening the data, to put it in a matrix format so that all similar values are in one column. This is necessary, for example, to create a chart or a report.
Note:
Syntax: Table.Unpivot(table as table, pivotColumns as list, attributeColumn as text, valueColumn as text) as table
Table.Unpivot translates a set of columns in a table into attribute-value pairs, combined with the rest of the values in each row.
Reference:
https://docs.microsoft.com/en-us/power-query/unpivot-column
https://docs.microsoft.com/en-us/powerquery-m/table-unpivot
DRAG DROP
You are modeling data in table named SalesDetail by using Microsoft Power Bl.
You need to provide end users with access to the summary statistics about the SalesDetail dat a. The users require insights on the completeness of the data and the value distributions.
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.
You have a large dataset that contains more than 1 million rows. The table has a datetime column named Date.
You need to reduce the size of the data model.
What should you do?
- A . Round the hour of the Date column to startOfHour.
- B . Change the data type of the Date column to Text.
- C . Trim the Date column.
- D . Split the Date column into two columns, one that contains only the time and another that contains only the date.
D
Explanation:
We have to separate date & time tables. Also, we don’t need to put the time into the date table, because the time is repeated every day.
Split your DateTime column into a separate date & time columns in fact table, so that you can join the date to the date table & the time to the time table. The time need to be converted to the nearest round minute or second so that every time in your data corresponds to a row in your time table.
Reference: https://intellipaat.com/community/6461/how-to-include-time-in-date-hierarchy-in-power-bi
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 to.
You need to prevent report recipients from reading the analyzed emails. The solution must minimize the model size.
What should you do?
- A . Implement row-level security (RLS) so that the report recipients can only see results based on the emails they sent.
- B . Remove the Subject and Body columns during the import.
- C . From Model view, set the Subject and Body columns to Hidden.
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. NOTE: Each correct selection is worth one point.
- A . Group the impressions by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.
- B . Create one-to-many relationships between the tables.
- C . Create a calculated measure that aggregates by using the COUNTROWS function.
- D . Create a calculated table that contains Ad_id, Site_name, and Impression_date.
B, C
Explanation:
Grouping in power query reduces the number of rows in the impression table that is gonna be loaded in the model. Creating relationships doesn’t increase the size of the model.
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?
- A . video
- B . custom streaming data
- C . text box
- D . web content
D
Explanation:
https://docs.microsoft.com/en-us/stream/portal-embed-video
https://docs.microsoft.com/en-us/power-bi/create-reports/service-dashboard-add-widget#add-web-content
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?
- A . Show whitespace
- B . Column profile
- C . Column distribution
- D . Column quality
D
Explanation:
Column quality: In this section, we can easily see valid, Error and Empty percentage of data values associated with the Selected table.
Note: In Power Query Editor, Under View tab in Data Preview Section we can see the following data profiling functionalities:
✑ Column quality
✑ Column distribution
✑ Column profile
Reference: https://community.powerbi.com/t5/Community-Blog/Data-Profiling-in-Power-BI-Power-BI-Update-April-2019/ba-p/674555
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 number
You import the list into Power Query Editor.
You need to ensure that the list contains records for each State/Region 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.
- A . Open the Advanced Editor.
- B . Select Column quality.
- C . Enable Column profiling based on entire dataset.
- D . Select Column distribution.
- E . Select Column profile.
CE
Explanation:
In Power query, the load preview by default is 1000 row. By default, the column quality also only looks at the first 1000 row. You can verify this by the status bar at the bottom of the Power query window. To change the profiling so it analyses the entire column of data, select the profiling status in the status bar. Then select Column profiling based on the entire data set.
https://theexcelclub.com/data-profiling-views-in-power-query-excel-and-power-bi/
HOTSPOT
You have an API that returns more than 100 columns.
The following is a sample of column names.
✑ client_notified_timestamp
✑ client_notified_source
✑ client_notified_sourceid
✑ client_notified_value
✑ client_responded_timestamp
✑ client_responded_source
✑ client_responded_sourceid
✑ client_responded_value
You plan to include only a subset of the returned columns.
You need to remove any columns that have a suffix of sourceid.
How should you complete the Power Query M code? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: Table.RemoveColumns
When you do “Remove Columns” Power Query uses the Table.RemoveColumns function
Box 2: List.Select
Get a list of columns.
Box 3: Text.EndsWith
DRAG DROP
You are building 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.
Explanation:
1- Convert list to table
2- Expand Column
3- Set Date type
Here is an example: https://youtu.be/B4kzyxnhQfI
The definition of the function which expand columns: https://docs.microsoft.com/en-us/powerquery-m/table-expandrecordcolumn
You import two Microsoft Excel tables named Customer and Address into Power Query.
Customer contains the following columns:
✑ Customer ID
✑ Customer Name
✑ Phone
✑ Email Address
✑ Address ID
Address contains the following columns:
✑ Address ID
✑ Address Line 1
✑ Address Line 2
✑ City
✑ State/Region
✑ Country
✑ Postal Code
The Customer ID and Address ID columns represent unique rows.
You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer.
What should you do?
- A . Merge the Customer and Address tables.
- B . Transpose the Customer and Address tables.
- C . Group the Customer and Address tables by the Address ID column.
- D . Append the Customer and Address tables.
A
Explanation:
There are two primary ways of combining queries: merging and appending.
When you have one or more columns that you’d like to add to another query, you merge the queries.
When you have additional rows of data that you’d like to add to an existing query, you append the query.
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data
You have the following three versions of an Azure SQL database:
✑ Test
✑ Production
✑ Development
You 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.
Which should you do?
- A . Create a JSON file that contains the database server names. Import the JSON file to the dataset.
- B . Create a parameter and update the queries to use the parameter.
- C . Create a query for each database server and hide the development tables.
- D . Set the data source privacy level to Organizational and use the ReplaceValue Power Query M function.
B
Explanation:
https://docs.microsoft.com/en-us/learn/modules/create-manage-workspaces-power-bi/4-development-lifecycle-strategy
You have a CSV file that contains user complaints. The file contains a column named Logged. Logged
contains the date and time each compliant 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?
- A . Change the data type of the Logged column to Date.
- B . Apply a transform to extract the last 11 characters of the Logged column and set the data type of the new column to Date.
- C . Create a column by example that starts with 2018-12-31 and set the data type of the new column to Date.
- D . Apply a transform to extract the first 11 characters of the Logged column.
You have an Azure SQL database that contains sales transactions. The database is updated frequently.
You need to generate reports from the data to detect fraudulent transactions. The data must be visible within five minutes of an update.
How should you configure the data connection?
- A . Add a SQL statement.
- B . Set Data Connectivity mode to DirectQuery.
- C . Set the Command timeout in minutes setting.
- D . Set Data Connectivity mode to Import.
B
Explanation:
With Power BI Desktop, when you connect to your data source, it’s always possible to import a copy of the data into the Power BI Desktop. For some data sources, an alternative approach is available: connect directly to the data source using DirectQuery.
DirectQuery: No data is imported or copied into Power BI Desktop. For relational sources, the selected tables and columns appear in the Fields list. For multi-dimensional sources like SAP Business Warehouse, the dimensions and measures of the selected cube appear in the Fields list. As you create or interact with a visualization, Power BI Desktop queries the underlying data source, so you’re always viewing current data.
Reference: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
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?
- A . The user will see data for which the State Province value is Kentucky and the Calendar Year is 2020.
- B . The user will see data for which the State Province value is Kentucky or the Calendar Year is 2020.
- C . The user will see only data for which the State Province value is Kentucky.
- D . The user will receive an error and will not be able to see the data in the report.
B
Explanation:
When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters.
Reference: https://docs.microsoft.com/en-us/power-bi/guidance/rls-guidance
HOTSPOT
Your company has affiliates who help the company acquire customers.
You build a report for the affiliate managers at the company to assist them in understanding affiliate performance.
The managers request a visual showing the total sales value of the latest 50 transactions for each affiliate. You have a data model that contains the following tables.
You need to develop a measure to support the visual.
How should you complete the DAX expression? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: CALCULATE
Start with CALCULATE and use a SUMX.
CALCULATE evaluates an expression in a modified filter context.
Box 2: SUM
Box 3: TOPN
TOPN returns the top N rows of the specified table.
Box 4: [TransactionDate]
TOPN Syntax: TOPN(<n_value>, <table>, <orderBy_expression>, [<order>[, <orderBy_expression>, [<order>]]…])
The orderBy_expression: Any DAX expression where the result value is used to sort the table and it is evaluated for each row of table.
Reference: https://docs.microsoft.com/en-us/dax/topn-function-dax
HOTSPOT
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.
You need to ensure that you can analyze sales by product category.
How should you configure the relationships from Products to ProductCategory? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.
Explanation:
Box 1: One-to-many
Box 2: Both
For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional).
Note:
Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
DRAG DROP
You have a Microsoft Power BI data model that contains three tables named Sales, Product, and Date.
The Sales table has an existing measure named [Total Sales] that sums the total sales from the Sales table.
You need to write a calculation that returns the percentage of total sales that a selected ProductCategoryName value represents. The calculation must respect any slicers on ProductCategoryName and must show the percentage of visible total sales. For example, if there are four ProductCategoryName values, and a user filters one out, a table showing ProductCategoryName and the calculation must sum up to 100 percent.
How should you complete the calculation? 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. NOTE: Each correct selection is worth one point.
Explanation:
Divide, Calculate, AllSelected.
Reference: https://docs.microsoft.com/en-us/dax/allselected-function-dax
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?
- A . Use the Auto Date/Time functionality in Microsoft Power BI and do NOT import the Date table.
- B . Duplicate the Date query in Power Query and use active relationships between both Date tables.
- C . 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.
- D . Create an active relationship between Sales and Date for the purchase date and an inactive relationship for the ship date.
D
Explanation:
Only one relationship can be active.
Note: If you query two or more tables at the same time, when the data is loaded, Power BI Desktop attempts to find and create relationships for you. The relationship options Cardinality, Cross filter direction, and Make this relationship active are automatically set.
Reference: https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
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 scenario, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
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 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?
- A . Yes
- B . No
A
Explanation:
Instead create a percentile line by using the Salary measure and set the percentile to 50%.
Note: The 50th percentile is also known as the median or middle value where 50 percent of observations fall below.
Reference: https://dash-intel.com/powerbi/statistical_functions_percentile.php