examtopics Flashcards
https://www.examtopics.com/exams/microsoft/70-778/view/ (157 cards)
1
Q
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 have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.
Date contains one column named Date.
The tables have the following relationships:
✑ Sales[DueDate] and Date[Date]
✑ Sales[ShipDate] and Date[Date]
✑ Sales[OrderDate] and Date[Date]
The active relationship is on Sales[DueDate].
You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.
Solution: You create a calculated table. You create a measure that uses the new table.
Does this meet the goal?
A) Yes
B) No
A
B
2
Q
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 have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.
Date contains one column named Date.
The tables have the following relationships:
✑ Sales[DueDate] and Date[Date]
✑ Sales[ShipDate] and Date[Date]
✑ Sales[OrderDate] and Date[Date]
The active relationship is on Sales[DueDate].
You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.
Solution: You create measures that use the CALCULATE, COUNT, and FILTER DAX functions.
Does this meet the goal?
A. Yes
B. No
A
A
3
Q
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 have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.
Date contains one column named Date.
The tables have the following relationships:
✑ Sales[DueDate] and Date[Date]
✑ Sales[ShipDate] and Date[Date]
✑ Sales[OrderDate] and Date[Date]
The active relationship is on Sales[DueDate].
You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.
Solution: You create two copies of the Date table named ShipDate and OrderDateGet. You create a measure that uses the new tables.
Does this meet the goal?
A. Yes
B. No
A
B
4
Q
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 have a user named User1. User1 is a member of a security group named Contoso PowerBI.
User1 has access to a workspace named Contoso Workspace.
You need to prevent User1 from exporting data from the visualizations in Contoso Workspace.
Solution: From the Microsoft Office 365 Admin center, you remove User1 from the All Users security group.
Does this meet the goal?
A. Yes
B. No
A
B
5
Q
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 have a user named User1. User1 is a member of a security group named Contoso PowerBI.
User1 has access to a workspace named Contoso Workspace.
You need to prevent User1 from exporting data from the visualizations in Contoso Workspace.
Solution: From the Microsoft Office 365 Admin center, you modify the properties of Contoso PowerBI.
Does this meet the goal?
A. Yes
B. No
A
A
6
Q
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 have a user named User1. User1 is a member of a security group named Contoso PowerBI.
User1 has access to a workspace named Contoso Workspace.
You need to prevent User1 from exporting data from the visualizations in Contoso Workspace.
Solution: From the PowerBI setting, you modify the Developer Settings.
Does this meet the goal?
A. Yes
B. No
A
B
7
Q
You plan to embed multiple visualizations in a public website.
Your Power BI infrastructure contains the visualizations configured as shown in the following table.
Visualization Name | Characteristc
Visual1 | Uses row-level security (RLS)
Visual2 | Uses a dataset that is stored
in Microsoft OneDrive for
Business
Visual3 | Contained in a report that was
shared to your user account
Visual4 | Is a custom Visual
Visual5 | Uses a dataset from an
on-premises Microsoft SQL
Server Analysis Services
(SSAS) database
Which two visualizations can you embed into the website? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. Visual 1 B. Visual 2 C. Visual 3 D. Visual 4 E. Visual 5
A
Visual2, Visual4
8
Q
You have a workspace that contains 10 dashboards. A dashboard named Sales Data displays data from two datasets. You discover that users are unable to find data on the dashboard by using natural language queries.
You need to ensure that the users can find data by using natural language queries.
What should you do?
A. From the settings of the workspace, modify the
Language Settings.
B. From the Sales Data dashboard, set the dashboard
as a Favorite.
C. From the properties of the datasets, modify the Q&A
and Cortana settings.
D. From the properties of the dashboard, modify the
Q&A settings.
A
C
9
Q
DRAG DROP -
You have a Microsoft Excel workbook that contains two tables.
From Power BI, you create a dashboard that displays data from the tables.
You update the tables each day.
You need to ensure that the visualizations in the dashboard are updated daily.
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.
NOTE. More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Select and Place:
1.: Download and install an on-premises data Gateway
(personal)
2.: Configure the Gateway Connection Settings for the
dataset
3.: Add subscriptions for the report.
4.: Download and install Power BI Desktop.
5. Configure the Schedule Refresh Settings for the
dataset
A
- -> 2. -> 5.
10
Q
You manage a Power BI model that has two tables named Sales and Product.
You need to ensure that a sales team can view only data that has a CountryRegionName value of Unites States and a ProductCategory value of Clothing.
What should you do from Power BI Desktop?
A. Add the following filters to a report.
CountryRegionName is United States
ProductCategory is Clothing
B. From Power BI Desktop, create a new role that has
the following filters. [CountryRegionName] =
“United States” [ProductCategory] = “Clothing”
C. Add the following filters in Query Editor.
CountryRegionName is United States
ProductCategory is Clothing
D. From Power BI Desktop, create a new role that has
the following filter. [CountryRegionName] = “United
States” && [ProductCategory] = “Clothing”
A
D
11
Q
You create a report in the Power BI service that displays the following visualizations:
✑ A KPI that displays the count of customers
✑ A table that displays the count of customers by country
✑ A line chart that displays the count of customers by year
You need to receive an alert when the total number of customers reaches 10,000.
What should you do first?
A. Pin the line chart to a dashboard.
B. Pin the KPI to a dashboard.
C. Embed the report into a Microsoft SharePoint page.
D. Pin the report to a dashboard
A
D
12
Q
You have a Power BI dashboard that displays different visualizations of company sales.
You enable Q&A on the dashboard.
You need to provide users with sample questions that they can ask when using Q&A.
Which settings should you modify form the Power BI Settings?
A. Subscriptions
B. Workbooks
C. Dashboards
D. Datasets
A
D
13
Q
You have an app workspace named Retail Store Analysis in the Power BI service.
You need to manage the members that have access to the app workspace using the least amount of administrative effort.
What should you do?
A. From the Office 365 Admin center, click Users.
B. From the Power BI Admin portal, click Tenant
settings.
C. From the Power BI Admin portal, click Usage
metrics.
D. From the Office 365 Admin center, click Groups.
A
D
14
Q
Your organization has a Microsoft Office 365 subscription.
When the users attempt to access the Power BI Service, they receive the error message shown in the exhibit.
Sorry…
we can’t finish signing you up
You need to ensure that all the users can access the Power BI service.
What should you do first?
A. From the properties of each dashboard, modify the Share dashboard settings.
B. From Microsoft Azure PowerShell, run the Set-MsolDomain cmdlet.
C. Instruct each user to install Microsoft Office 2016.
D. From Microsoft Azure PowerShell, run the Set-MsolCompanySettings cmdlet.
E. From Power BI Admin portal, modify the Tenant settings.
F. From the Microsoft Azure Active Directory admin center, assign a Power BI (free) license to each user.
G. From the Power BI Admin portal, modify the Privacy Settings.
A
D
15
Q
You have a Microsoft Excel spreadsheet that contains a table named Sales.
You need to add the Sales table to a Power BI dashboard as a tile.
How should you configure the tile?
A. From the Power BI service, import the data from the
Excel workbook.
B. From Excel, publish the workbook to the Power BI
service.
C. From the Power BI tab in Excel, pin the table.
D. From the Power BI service, upload the Excel
workbook.
A
C
16
Q
HOTSPOT -
Your company plans to use Power BI for 20 users in the sales department. The users will perform the following tasks:
✑ Access a published Power BI app.
Modify reports in an app workspace.
✑ Share dashboards created in My Workspace.
You need to identify which Power BI licenses are required for the tasks. The solution must use the Power BI (free) license, whenever possible.
Which license should you identify for each task? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
- Access a published Power BI app: A) free
B) Pro - Modify Reports in an app Workspace: A) free
B) Pro - Share daschboards created in My Workspace:
A) free
B) Pro
A
- : A
- : B
- : B
17
Q
DRAG DROP -
From Power BI service, you publish an app that contains one dashboard and one report. Q&A is enabled on the dashboard.
In Q&A, a user types the query count of clients and fails to receive any results. The user then types the query count of subscribers and receives the expected results.
You need to ensure that the user can use both queries to receive the same results.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
1. Edit the Dashboard Settings from powerbi.com
2. Enable and configure Data calssification for
Dashboards
3. Publish the report to App Workspaces
4. Update the app from powerbi.com
5. Edit the synonyms
6. Open the report by using Power BI Desktop
7. Delete and pubish the app
A
- -> 5. -> 3. -> 4.
18
Q
DRAG DROP -
You have a table named Sales. Sales contains the data shown in the following table.
Year | Total Sales
2015 | 123445
2016 | 23552
You have the following measure.
Total Sales This Year = SUM([Total Sales])
You plan to create a KPI to compare the current yearly sales to the previous year as shown in the exhibit.
(KPI Visual)
You need to create the measure for the goal.
How should you complete the DAX formula? 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.
Select and Place:
- Calculate
- Dateadd
- Previousyear
- Sameperiodlastyear
- Sumx
VALUE1 ([Total Sales This Year],
VALUE2 (‘Date[Date], -1, Year))
A
VALUE1: Calculate
VALUE2: Dateadd
19
Q
You are creating a report in Power BI Desktop.
You are consuming the following tables.
Sales (SalesID [int], SalesDate [datetime], TotalPrice [float], CustomerID [int], SalesShipDate [datetime], StoreID [int])
Date ( Date [datetime], DateKey [int], DateName [datetime], MonthNumber [int], Week [int], MonthName [varcahr3] Year [int])
Date[Date] is in the mm/dd/yyyy format. Date[DateKey] is in the ddmmyyyy format. Date[MonthNumber] is in the mm format. Date[MonthName] is in the mmm format.
You create the report shown in the exhibit.
Balken SalesCount to Month
You need to ensure that the months appear in the order of the calendar.
How should you sort the MonthName column?
A. by MonthNumber
B. ascending
C. descending
D. by DateKey
A
A
20
Q
ou are creating a report in Power BI Desktop.
You are consuming the following tables.
Sales (SalesID [int], SalesDate [datetime], TotalPrice [float], CustomerID [int], SalesShipDate [datetime], StoreID [varchar100])
Date ( Date [datetime], DateKey [int], DateName [datetime], MonthNumber [int], Week [int], MonthName [varcahr3] Year [int])
You have a new table named Fiscal that has the same schema as the Date table, but contains the fiscal dates of your company.
You need to create a report that displays the total sales by fiscal month and calendar month.
What should you do?
A. Union Fiscal and Date as one table.
B. Add Fiscal to the model and create a one-to-many
relationship by using Date[Year] and Fiscal[Year].
C. Add Fiscal to the model and create a one-to-one
relationship by using Date[Year] and Fiscal[Year].
D. Merge Fiscal into the Date table.
A
D
21
Q
You plan to create a Power BI report. You have the schema model shown in the exhibit.
star-Schema of Sales.jpg
The model has the following relationships:
✑ Store to District based on DistrictID
Sales to Store based on LocationID
✑ Sales to Date based on PeriodID
✑ Sales to Item based on ItemID
You configure row-level security (RLS) so that the district managers of the stores only see the sales from the stores they manage.
When the district managers view the Store by Items report, they see items for all the stores.
You need to ensure that the district managers can see items for the stores they manage only.
How should you configure the relationship from Sales to Item?
A. Select Assume Referential Integrity.
B. Change the Cardinality to One to Many (1:*).
C. Change the Cross filter direction to Both.
D. Change the Cardinality to One to one (1:1).
A
C
22
Q
You use Power BI Desktop to create a visualization for a Microsoft SQL Server data source.
You need to ensure that you can use R visualization.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Download and install Microsoft R Server.
B. Download and install RStudio Server on the
computer that has Power BI Desktop installed.
C. Install SQL Server R Services on the server that runs
SQL Server.
D. Enable R Scripting on the computer that has Power
BI Desktop installed.
E. Download and install Microsoft R on the computer
that has Power BI Desktop installed.
A
D, E
23
Q
You have a Power BI model that contains the following two tables:
✑ Sales(Sales_ID, sales_date, sales_amount, CustomerID)
✑ Customer(CustomerID, First_name, Last_name)
There is a relationship between Sales and Customer.
You need to create a measure to rank the customers based on their total sales amount.
Which DAX formula should you use?
A. RANKX(ALL(Sales), SUMX(RELATEDTABLE(Customer), [Sales_amount]))
B. TOPN(ALL(customer), SUMX(RELATEDTABLE(Sales), [Sales_amount]))
C. RANKX(ALL(customer), SUMX(RELATEDTABLE(Sales), [Sales_amount]))
D. RANK.EQ(Sales[sales_amount], Customer[CustomerID])
A
C
24
Q
You have a Microsoft SharePoint Online site named Sales.
Your company has 1,000 sales users. All the sales users can access Sales.
You create a report in an app workspace in the Power BI service. You embed the report into a page on the Sales site by using the Power BI web part.
You need to ensure that all the sales users can view the report from the Sales site.
What should you do?
A. Configure the Portal Site Connection for the Sales
site.
B. Enable anonymous access for the Sales site.
C. Configure the app workspace for Premium capacity.
D. Disable the Embed content in apps setting from the
Tenant settings in Power BI.
A
C
25
You plan to deploy a Power BI app workspace that will be viewed by 10,000 users.
You need to ensure that dashboard data can be updated every 30 minutes.
What should you do?
A. Assign each user a Power BI Pro license.
B. Store the dataset in Microsoft Azure Storage that
uses the Premium storage tier.
C. Create the app workspace by using an account that
is assigned a Power BI Pro license.
D. Configure the app workspace for Premium capacity.
D
26
You have a Microsoft Excel 2016 workbook that has a Power Pivot model. The model contains the following tables:
✑ Product (Product_id, Product_Name)
✑ Sales (Order_id, Order_Date, Product_id, Salesperson_id, Sales_Amount)
✑ Salesperson (Salesperson_id, Salesperson_name, address)
The model has the following relationships:
✑ Sales to Product
✑ Sales to Salesperson
You create a new Power BI file and import the Power Pivot model.
You need to ensure that you can generate a report that displays the count of products sold by each salesperson.
What should you do before you create the report?
A. Create a one-to-one relationship between Product and Salesperson.
B. For each relationship, change the Cross filter direction to Both.
C. For each relationship, change the Cardinality to One to one (1:1).
D. Change a many-to-one relationship between Product and Salesperson.
B
27
You have a Power BI model that contains the following two tables:
✑ Sales (Sales_ID, DateID, sales_amount)
✑ Date(DateID, Date, Month, Week, Year)
The tables have a relationship.
You need to create a measure to calculate the sales for same period from the previous year.
Which DAX formula should you use?
A. SUM(sales[sales_amount]) -
CALCULATE(SUM(sales[sales_amount]),
DATESYID('Date'[Date]))
B. CALCULATE(SUM(sales[sales_amount]),
SAMEPERIODLASTYEAR('Date'[Date]))
C. SUM(sales[sales_amount]) ""
CALCULATE(SUM(sales[sales_amount]),
SAMEPERIODLASTYEAR('Date'[Date]))
D. CALCULATEx(SUM(sales(sales_amount]),
DATESYID('Date'[Date]))
B
28
You plan to develop a Power BI report that has a bar chart to display the number of customers by location.
You have a table named Customer that has the following columns:
✑ CustomerID
✑ CustomerName
✑ Address
✑ City
✑ ProvState
✑ Country
You need to allow users to drill down by location. The report will display the number of each customer by Country, and drill down to ProvState, and then to City.
How should you configure the drill down in the bar chart?
A. In the Legend field, add Country. In the Axis field,
add ProvState at the top, followed by City.
B. In the Value field, add Country at the top, followed
by ProvState, and then City.
C. In the Value field, add Country. In the Legend field,
add ProvState at the top, followed by City.
D. In the Axis field, add Country at the top, followed by
ProvState, and then City
D
29
You have a table named Sales that contains sales data for the United States. A sample of the data in Sales is shown in the following table.
```
Zone; Year; SalesAmount
Oregon; 2015; 100000
Oregon; 2016; 200000
California; 2015; 300000
California; 2016; 500000
Washington; 2016; 400000
```
When you attempt to create a map that shows SalesAmount by Zone, you discover that the map shows a bubble based on cities instead of states.
You need to ensure that the map shows bubbles based on states.
What should you do?
A. Add a column named Country that contains United
States as the value.
B. Add a column for longitude and a column for
latitude.
C. Select the Zone field. From the Modeling tab,
change the Data Category.
D. Select the Zone field. From the Modeling tab,
change the Data Type.
C
30
You have two tables named CustomerVisits and Date in a Power BI model.
You create a measure to calculate the number of customer visits. You use the measure in the report shown in the exhibit. (Click the Exhibit button.)
Visual BarChart of Month
You discover that the total number of customer visits was 60,000, and that there were only 5,000 customer visits in August.
You need to fix the report to display the correct data for each month.
What should you do?
A. Modify the measure to use the CALCULATE DAX function.
B. Create a relationship between the CustomerVisits table and the Date table.
C. Modify the measure to use the sum DAX function.
D. Create a hierarchy in the Date table.
B
31
You have the following tables.
```
Table name; Column name; Data Type
Subscriber; SubscriberID; Wohle Number
; StartDate; Date
; EndDate; Date
Date; Date; Date
; Day; Text
; Month; Text
; Year; Wohle Number
```
There is a many-to-one relationship from Subscriber to Date that uses Subscriber[StartDate] and Date[Date]. The Cross filter direction of the relationship is set to
Single.
You plan to create a column chart that displays the following two measures:
✑ Count of SubscriberID by Month based on the StartDate
✑ Count of SubscriberID by Month based on the EndDate
What should you do before you create the measures?
A. Create an active one-to-one relationship from
Subscriber[StartDate] to Date[Date].
B. Change the Cross filter direction of the active
relationship to Both.
C. Change the active relationship for many-to-one.
D. Create an inactive many-to-one relationship from
Subscriber[StartDate] to Date[Date].
E. Create an inactive many-to-one relationship from
Subscriber[EndDate] to Date[Date].
E
32
You have a Power BI report that displays a bar chart and a donut chart on the same page. The bar chart shows the total sales by year and the donut chart shows the total sale by category.
You need to ensure that when you select a year on the bar chart, the donut chart remains unchanged.
What should you do?
A. Edit the interactions from the Format menu.
B. Set a visual level filter on the bar chart.
C. Set a visual level filter on the donut chart.
D. Add a slicer to the page that uses the year column.
A
33
You have a Power Pivot model that includes a KPI.
You need to create a visualization based on the Power Pivot model as shown in the exhibit.
Table with columns:
Year; Month; RevenueYT RevenueYT Goal; RevenueYT Status
```
Which type of visualization should you use?
A. matrix
B. KPI
C. multi row card
D. table
```
A
34
You plan to create a report in Power BI Desktop.
You have the following tables.
Sales (OrderID, Product ID, ProductCategory, ProductSubCategory, OrderDate, SalesAmount)
Date(DateID, Date, Year, Month, Week, Day)
You have a measure that uses the following DAX formula.
Total Sales = SUM('Sales'[SalesAmount])
You plan to create a report to display TotalSales by ProductCategory and ProductSubCategory.
You need to create a measure to calculate the percentage of TotalSales for each ProductCategory.
How should you complete the DAX formula?
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.
Options for Value: ALL, ALLEXCEPT, ALLSELECTED, CALCULATE, DIVIDE, Product, ProductCategory, ProductSubcategory
Measure1 =
Value ([TotalSales], CALCULATE([TotalSales],
Value (Sales[Value], Sales[Value])))
Measure1 =
DIVIDE ([TotalSales], CALCULATE([TotalSales],
ALLSELECTED (Sales[ProductCategory], Sales[ProductSubcategory])))
35
You plan to use Power BI to import data from 2013 to 2015.
Product Subcategory[Subcategory] contains NULL values.
End of Repeated Scenario.
You implement the Power BI model.
You need to add a new column to the Product Subcategory table that uses the following formula.
=if [Subcategory]=null then "NA" else [Subcategory]
Which command should you use in Query Editor?
A. Conditional Column
B. Column From Examples
C. Invoke Custom Function
D. Custom Column
A
36
End of Repeated Scenario.
You are implementing the Power BI model.
You need to edit the Product Category query to match the desired Power BI model.
How should you complete the advanced query? 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 pint.
Select and Place:
```
Options for VALUE:
Table.Combine
Table.RemoveColumns
Table.RemoveRows
Table.RenameColumns
Table.RecorderColumns
Table.SelectColumns
```
```
let
Source = SQL.Database("localhost"),
DB1 = Source([Name"DB1"]}[Data],
dbo_DimProductCategory = DB1{[Schema-"dbo",
Item="DimProductCategory"]} [Data],
#"Var1" = VALUE (dbo_DimProduct,
{"ProductCategoryAlternateKey",
"SpanishProductCategoryName",
"FrenchProductCategroyName"})
#"Var2" = VALUE (#"Var1",
{{"EnglishProductCategoryName", "Category"}}}
in
#"Var2"
```
```
let
Source = SQL.Database("localhost"),
DB1 = Source([Name"DB1"]}[Data],
dbo_DimProductCategory = DB1{[Schema-"dbo",
Item="DimProductCategory"]} [Data],
#"Var1" = Table.RemoveColumns (dbo_DimProduct,
{"ProductCategoryAlternateKey",
"SpanishProductCategoryName",
"FrenchProductCategroyName"})
#"Var2" = Table.RenameColumns (#"Var1",
{{"EnglishProductCategoryName", "Category"}}}
in
#"Var2"
```
37
End of Repeated Scenario.
You implement the Power BI model.
You need to create a hierarchy that has Category, Subcategory, and Product.
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.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Select and Place:
1. To the Product Subcategory, add a calculated
measure that uses the RELATED('Product
Categrory'[Category]) DAX function.
2. To the Product table, add a column named Category
that uses the RELATED('Product Category'[Category])
DAX function.
3. To the Product table, add a calculated measure that
uses the RELATED('Product Category'[Category])
DAX function.
4. Create a hierachy
5. To the Product table, add a column named
SubCategory that uses the RELATED('Product
Subcategory'[Subcategory]) DAX function.
6. To the Product Subcategory table, add a column
named Category that uses the RELATED('Product
Category'[ProductCategoryKey]) DAX function.
2. -> 5. -> 4.
38
End of Repeated Scenario.
You implement the Power BI model.
You need to add a measure to rank total sales by product. The results must appear as shown in the following table.
```
Rank; Product; SalesAmount
1; Product3; 130000
1; Product2; 130000
2; Product1; 120000
3; Product5; 10000
3; Product4; 10000
```
Which DAX formula should you use?
A. Product Ranking = RANKX(ALL('Product'),
[SalesAmount],,Asc, Dense)
B. Product Ranking = RANKX(ALL('Product'),
[SalesAmount],,DESC, Skip)
C. Product Ranking = RANKX(ALL('Product'),
[SalesAmount],,DESC, Dense)
D. Product Ranking = RANKX(Product,
[SalesAmount],,DESC, Skip)
C
39
End of Repeated Scenario.
You need to create a measure of Sales[SalesAmount] where Product[Color] is Red or Product[Size] is 50.
Which DAX formula should you use?
A: [Total Sales] := CALCULATE (SUM([SalesAmount]),
ALL('Product'[Color], 'Product['Size]))
B: [Total Sales] := Calculate ( SUM([SalesAmount]),
'Product'[Color]="Red" || 'Product'[Size] = 50
C: [Total Sales] := Calculate ( SUM([SalesAmount]),
FILTER ('Product',
'Product'[Color]="Red" ||
'Product'[Size] = 50))
D: [Total Sales] := Calculate ( SUM([SalesAmount]),
FILTER( 'Product'[Color]="Red" || 'Product'[Size] = 50))
B
40
End of Repeated Scenario.
You implement the Power BI model.
You add another table named Territory to the model. A sample of the data is shown in the following table.
```
TerritoryKey; TerritoryName
1; United States
1; USA
2; Canada
2; Can
3; United Kingdom
3; UK
```
You need to create a relationship between the Territory table and the Sales table.
Which function should you use in the query for Territory before you create the relationship?
A. Table.Distinct
B. Table.IsDistinct
C. Table.ReplaceMatchingRows
D. Table.RemoveMatchingRows
A
41
End of Repeated Scenario.
You implement the Power BI model.
You plan to add a table named Date to the model. The table will have columns for the date, year, month, and end of the last month, and will include data from
January 1, 2013 to December 31, 2015.
The Date table and the Sales table will have a relationship.
Which DAX functions should you use to create the columns?
```
A. CALENDARAUTO, YEAR, MONTH, and EOMONTH
B. CALENDAR, YEAR, MONTH, and ENDOFMONTH
C. CALENDARAUTO, YEAR, MONTH, and
ENDOFMONTH
D. CALENDAR, YEAR, MONTH, and EOMONTH
```
D
42
You have two Microsoft SQL Server database servers named SQLProd and SQLDev. SQLDev contains the same tables as SQLProd, but only a subset of the data in SQLProd.
You create a new Power BI Desktop model that uses 120 tables from SQLDev.
You plan to publish the Power BI file to the Power BI service.
You need to connect the model to the tables in SQLProd. The solution must minimize administrative effort.
What should you do from Query Editor before you publish the model?
A. Create a new connection to SQLProd, and then
import the tables from SQLProd.
B. Delete the existing queries, and then add new data
sources.
C. Configure the Data source settings.
D. Edit the source of each table query.
C
43
DRAG DROP -
You have a customer table in Power BI Desktop. The customer table contains the columns as shown in the following table.
```
CustomerID; Display Name; SSN
1; Smith, John; 987-65-4321
2; Smith, Gail; 123-45-6789
3; White, Tony; 010-20-4567
4; Mark, Keith; 890-67-5432
```
You need to create a custom column that hides the first three digits of the SSN. The values in the new column must have the xxx-99-9999 format.
How should you complete the Query Editor formula? 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.
Select and Place:
```
Options for VALUE:
Text.End
Text.Insert
Text.Range
Text.Replace
Text.Start
```
VALUE ([SSN], VALUE ([SSN],4), "xxx-")
Text.Replace([SSN], Text.Start([SSN],4), "xxx-")
44
You have a Power BI model that has a date table. A sample of the data shown in the following table.
```
Date; Day; Week; Month; Year
2014-12-01; 1; 27; 12; 2014
2014-12-02; 2; 27; 12; 2014
2014-12-03; 3; 27; 12; 2014
2014-12-04; 4; 27; 12; 2014
```
You need to add a column to display the date in the format of December 01, 2014.
Which DAX formula should you use in Power BI Desktop?
A. FORMAT([Date], "MMM") & " " & FORMAT([Date],
"DD") & ", " & FORMAT([Date], "YYYY")
B. FORMAT([Date], "M") & " " & FORMAT([Date], "D") & ",
" & [Date].[Year])
C. [Date].[Month] & " " & FORMAT([Date], "D") & ", " &
[Date].[Year])
D. FORMAT([Date], "MMMM, DD, YYYY")
D
45
HOTSPOT -
You have a query that retrieves data from a Microsoft Azure SQL database.
You discover that a column named ErrorCode has several values starting with a space character, and a column named SubStatus contains several non-printable characters.
You need to remove all the leading whitespaces from ErrorCode and all the non-printable characters from SubStatus. All other data must be retained.
What should you do on each column? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
```
ErrorCode:
A: From the Extract menu, click First Characters.
B: From the Extract menu, click Length.
C: From the Extract menu, click Clean.
D: From the Extract menu, click Trim.
```
```
SubStatus:
A: From the Extract menu, click First Characters
B: From the Extract menu, click Length
C: From the Extract menu, click Clean
D: From the Extract menu, click Trim
```
ErrorCode: D
SunStatus: C
46
From Power BI Desktop, you create a query that imports the following table.
```
City
UK - London
France - Paris
Spain - Madrid
Canada - Montreal
```
You need to configure the table to appear as shown in the following table:
```
City
London
Paris
Madrid
Montreal
```
What should you do?
A. From the Format menu, click Trim.
B. From the Extract menu, click Last Characters.
C. From the Split Column menu, click By Delimiter.
D. From the Extract menu, click Text After Delimiter.
D
47
You plan to create several datasets by using the Power BI service.
You have the files configured as shown in the following table.
```
File name; File type; Size; Location
Data 1; TSV; 50 MB; Microsoft OneDrive
Data 2; XLSX; 3 GB; Local
Data 3; XML; 100 MB; Microsoft OneDrive for Business
Data 4; CSV; 2 GB; Microsoft OneDrive
Data 5; JPG; 5 MN; Local
```
You need to identify which files can be used as datasets.
Which two files should you identify? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
```
A. Data 1
B. Data 2
C. Data 3
D. Data 4
E. Data 5
```
B, D
48
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 have an app workspace that contains a report. The report contains sensitive data.
You need to ensure that you can embed the report into a custom application that will be accessed by external users. The external users will NOT have a Microsoft
Azure Active Directory user account or Power BI licenses.
Solution: From Publish to web, generate an iFrame.
Does this meet the goal?
A. Yes
B. No
B
49
ote: 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 have an app workspace that contains a report. The report contains sensitive data.
You need to ensure that you can embed the report into a custom application that will be accessed by external users. The external users will NOT have a Microsoft
Azure Active Directory user account or Power BI licenses.
Solution: Configure the app workspace to be read-only for members and to run in a shared capacity.
Does this meet the goal?
A. Yes
B. No
B
50
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 have an app workspace that contains a report. The report contains sensitive data.
You need to ensure that you can embed the report into a custom application that will be accessed by external users. The external users will NOT have a Microsoft
Azure Active Directory user account or Power BI licenses.
Solution: Purchase Power BI Premium P1, and then configure the app workspace to run in a dedicated capacity.
Does this meet the goal?
A. Yes
B. No
A
51
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 have a Microsoft Excel workbook that is saved to Microsoft SharePoint Online. The workbook contains several Power View sheets.
You need to recreate the Power View sheets as reports in the Power BI service.
Solution: From Excel, click Publish to Power BI, and then click Export.
Does this meet the goal?
A. Yes
B. No
B
52
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 have a Microsoft Excel workbook that is saved to Microsoft SharePoint Online. The workbook contains several Power View sheets.
You need to recreate the Power View sheets as reports in the Power BI service.
Solution: From the Power BI service, get the data from SharePoint Online, and then click Import.
Does this meet the goal?
A. Yes
B. No
A
53
There is the table "Discount" with Attribute Discount defined by this DAX:
Discount = GENERATESERIES(0, 0.5, 0.1)
```
Discount[Discount] was created by using the [answer choice] command.
A: New Column
B: New Measure
C: New Parameter
D: New Table
```
```
The maximum value for the Discount slicer is:
A: 0.1
B: 0.5
C: 1
D: 50
```
D, B
54
HOTSPOT -
You create an app workspace named Wingtip Sales. Wingtip Sales is configured as shown in the following exhibit.
Name: Wintip Sales
WorkspaceID: wingtipsales
Private - Only approved members can see what's inside
Members can edit Power BI Content
Members:
austin -> Admin
maxwell -> Member
James -> Member
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.
Hot Area:
The one Task that Austin is permitted to perform, but the other members are not permitted to perform is?
A: add other users as members
B: create a new Dashboard
C: pin a report visualisation to a Dashboard
D: publish a PBIX file
To make the Content in Wingtip Sales available to users who have the free Power I License, you must first:
A: add all the users as Workspace members
B: change the app Workspace from Private to Public
C: purchase Power BI Premium
A, C
55
You open the Power BI Admin portal as shown in the following graphic.
AdminPortalForPowerBIEmbededCapacitySettings.jpg
All the app workspaces use the customer's capacity.
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
You can scale up the customers capacity by the:
A: pricing Tier from the Azure Portal
B: Settings of the Workspace
C: subscription from Office356 admin Center
D Tenant Settings from the PowerBI Admin Portal
When designing a custom application that embeds Reports from the customers capacity the developer:
A: can use both the user owns data model and the app
owns data model
B: must use the app owns data model
C: must use the user owns data model
A, B
56
Your company has several developers who plan to create custom solutions that will interact with the API for the Power BI service.
Which three operations can the developers achieve by using the API? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
```
A. Retrieve rows from a dataset
B. Create a dataset
C. Add rows to a dataset
D. Refresh an imported dataset
E. Add a member to a row-level security role
```
B, C, D
57
How to get Numbers like 0.60 displayed as 60% in PBI visuals?
A. Create a calculated column that adds the % symbol
to the values.
B. From the Modeling tab, change the Data Type to
Percentage.
C. Edit the query of the data source and change the
Data Type to Percentage.
D. Create a measure that adds the % symbol to the
values.
B, (D also possible)
58
You have a service published to a website.
When you connect to the website, you receive the following data.
Some XML Code
```
You need to create a query that retrieves the Categories data and the Customers data.
Which type of source should you use?
A. JSON
B. Text/CSV
C. OData Feed
D. XML
```
D
59
You are importing sales data from a Microsoft Excel file named Sales.xlsx into Power BI Desktop.
You need to create a bar chart showing the total sales amount by region.
When you create the bar chart, the regions appear as expected, but the sales amount value displays the count of sales amount instead of the sum of sales amount each region.
You need to modify the query to ensure that the data appears correctly.
What should you do?
A. Delete the query, import the data into Microsoft SQL
Server, and then import the data from SQL Server.
B. In Query Editor, add a calculated column that totals
the sales amount column.
C. Change the Data Type of sales amount column to
Numeric.
D. Refresh the data model.
C
60
You create a KPI visualization in Power BI Desktop that uses the month as the trend axis.
You discover that the data is not sorted by month.
You need to change the sort order of the visualization.
What should you do first?
A. Convert the visualization to a different type.
B. Remove the trend axis from the visualization.
C. Modify the visual level filters.
D. Modify the drillthrough filters.
A
61
You have a Microsoft SQL Server Analysis Services (SSAS) cube that contains historical data.
In Power BI Desktop, you have the following query for the cube.
```
let
Source = AnalysisSerqices.Database ("msi", "Test", [TypedMeasureColAs=true] ) ,
```
Model1 = Source { [Id="Model"] } [Data],
Model2 = Model1 { [Id="Model"] } [Data],
#"Added Items" = Cube.Transform(Model2,
{
…
}),
"Changed Type" = Table.TransformColumnTypes (#"Added
Items", { {"FactInternetSales .CarrierTrackingNumber", Int64.Trype} } ) ,
#"Removed Duplicates" = Table.Distinct (#"Changed Type", {"FactInternetSates.CarrierTrackingNumber"} ),
"Changed Type1" = Table.TransformColumnTypes (#"Removed Duplicates,
{ {"FactInternetSales .CustomerPONumber", Int64 .Type} })
in
#"Changed Type1"
The query retrieves 25,499 records.
When you check the data warehouse that is the source of the cube, you discover that there are 26,423 records.
You need to ensure that the query retrieves all 26,423 records.
What should you do?
A. From Query Editor, refresh all the data.
B. Change the query to use Live connection mode.
C. Delete the Remove Duplicates step.
D. Add an Unpivot Columns step.
C
62
You have a query that retrieves sales data. A sample of the data is shown in the following table.
Date | CustomerId | ProductId | Quantity
10/10/2016 | 8877 | 8878 | 5
null | 8877 | 8879 | 5
null | 8877 | 8879 | 5
10/11//2016 | 5723 | 8234 | 1
You need to ensure that the values in the Date column contain a date. Null values must be replaced with the date from the previous row.
What should you click on the Transform tab in Query Editor?
A. Format, and then Clean
B. Date, and then Earliest
C. Fill, and then Down
D. Replace Values, and then Replace Errors
C
63
Your organization has a Microsoft Office 365 subscription.
When the users attempt to access the Power BI Service, they receive the error message:
Sorry … We can't finish signing you up.
Your IT Department has turned off signup for Microsoft Power BI.
Contact them zo complete signup.
You need to ensure that all the users can access the Power BI service.
What should you do first?
A. From the Microsoft Azure Active Directory admin
center, assign a Power BI (free) license to each user.
B. From the Power BI Admin portal, modify the Tenant
settings.
C. From Microsoft Azure PowerShell, run the cmdlet:
Set-MsolCompanySettings
-AllowAdHocSubscriptions $true
D. From the properties of each dashboard, modify the
Share dashboard settings.
C
64
You plan to use Power BI Desktop to import 100 CSV files. The files contain data from different stores. The files have the same structure and are stored in a network share. You need to import the CSV files into one table. The solution must minimize administrative effort.
What should you do?
A. Add a folder data source and use the Combine Files
command.
B. Add a folder data source and use the Merge
Queries command.
C. Add a Microsoft Excel data source and use the
Merge Queries command.
D. Add text/CSV data sources and use the Append
Queries command.
A
65
You have the following two queries in Power BI Desktop:
-> A query named Query1 that retrieves a table named SMB_Customers from a Microsoft SQL Server database
-> A query named Query2 that retrieves a table named Enterprise_Customers from an Oracle database
Both tables have the same columns.
You need to combine the data from SMB_Customers and Enterprise_Customers.
Which command should you use?
A. Combine Files
B. Merge Queries
C. Merge Columns
D. Append Queries
D
66
You are creating a Power BI Desktop report that has several bar charts and a date slicer.
You need to create a slide show that can be viewed from the Power BI service. Each slide must display the charts filtered for a different year.
What should you do before you publish the report?
A. Configure report level filters, and then create
Groups that use the List group type.
B. Configure drillthrough filters for each bar chart, and
then select Selection Pane.
C. Filter the bar charts by using the slicer, and then
create bookmarks.
D. Configure page level filters, and then create groups
that use the Bin group type.
C
67
DRAG DROP -
You create a report in Power BI Desktop.
You need to embed the report into a Microsoft SharePoint Online site.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
1. Add a Webpart to a page
2. Pin a visualization
3. Pin a live page
4. Obtain an embed link for SharePoint
5. Publish the report to the Power BI service
5. -> 4. -> 1
68
You have a Power BI app named App1. The privacy for the App1 app workspace is set to Private.
A user named User1 reports that App1 does not appear in the My organization AppSource. App1 appears in the My organization AppSource for your account.
You need to ensure that User1 sees App1 from the My organization AppSource.
What should you do?
A. From the app workspace, click Update app,
configure the Access setting, and then click Update
app.
B. From the app workspace, share the dashboard.
C. From the app workspace settings, add a member.
D. From the app workspace, click Update app,
configure the Content settings, and then click
Update app.
A
69
You have a sales report in an app workspace. The report displays a map of sales by location and a bar chart of sales by year. The report has a slicer to filter the data by year.
You need to create a dashboard that contains visualizations. The solution must ensure that you can use the slicer to filter the data by year.
What should you do?
A. Pin each visualization to the dashboard, and then
add a web content tile.
B. Add a page level filter, and then pin each
visualization to the dashboard.
C. Publish the app workspace.
D. Pin the report as a live page.
A
70
A data analyst publishes several Power BI visualizations to a blog.
You discover that some of the visualizations contain data that is considered private by your company.
You need to prevent the visualizations from being published to the blog.
What should you do?
A. From the Power BI Admin portal, disable the Publish to web setting.
B. From the Power BI settings, delete the embedded codes.
C. From the Power BI Admin portal, disable the Share content with external users setting.
D. From the dashboard settings, modify the Share dashboard settings.
A
71
You have an app workspace that contains two datasets named dataset1 and dataset2. Dataset1 connects to a Microsoft Azure SQL database. Dataset2 connects to a Microsoft Excel file stored in Microsoft OneDrive for Business.
You create a report named Report1 that uses dataset1.
You pin Report1 to a dashboard named Dashboard1.
You publish the app workspace to all the users in your organization.
You need to delete dataset2 from the app workspace.
What should you do first?
A. Delete Dashboard1.
B. Delete Report1.
C. Unpublish the app.
D. Configure the refresh settings for Dataset2.
C
72
You create a report in the Power BI service.
You plan to provide external users with access to the report by publishing the report to a public blog.
You need to ensure that the report in the blog post will be updated as the data is refreshed.
What should you do in the Power BI service?
A. Publish the app workspace to the entire
organization. In the blog post, use the URL of the
workspace.
B. Share the report. In the blog post, use the URL of
the dashboard.
C. Publish the report to the web. In the blog post, use
the embed code URL.
D. In the blog post, use the URL of the report.
C
73
In the Power BI service, you create an app workspace that contains several dashboards.
You need to provide a user named user1@contoso.com with the ability to edit and publish dashboards.
What should you do?
A. Modify the members of the app workspace.
B. Configure security for the dataset used by the app.
C. Share the dashboard, and then modify the Access
settings of the dashboard.
D. From the app workspace, click Update app, and
then configure the Access settings.
A
74
You embed a Power BI report in a Microsoft SharePoint Online page.
A user named User1 can access the SharePoint Online page, but the Power BI web part displays the following error message: "This content isn't available."
User1 is unable to view the report.
You verify that you can access the SharePoint Online page and that the Power BI report displays as expected.
You need to ensure that User1 can view the report from SharePoint Online.
What should you do?
A. Publish the app workspace.
B. Share the dashboard in the app workspace.
C. Edit the settings of the Power BI web part.
D. Modify the members of the app workspace.
A
75
You have a Power BI model that contains the following two tables:
✑ Assets (AssetID, AssetName, Purchase_DateID, Value)
✑ Date (DateID, Date, Month, Week, Year)
The tables have a relationship. Date is marked as a date table in the Power BI model.
You need to create a measure to calculate the percentage that the total assets value increased since one year ago.
Which DAX formula should you use?
A. (sum(Assets[Value]) "" ALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR("˜Date'[Date]))) /
CALCULATE(sum(Assets[Value])),
SAMEPERIODLASTYEAR("˜Date'[Date])))
B. CALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR("˜Date'[Date])) /
(sum(Assets[Value])
C. CALCULATE(sum(Assets[Value]),DATESYTD(
("˜Date'[Date]))/ (sum(Assets[Value])
D. (sum(Assets[Value]) ""
CALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR("˜Date'[Date]))/
E. Total Assets Value Increase =
(
SUM ( Assets[Value] )
- CALCULATE ( SUM ( Assets[Value] ),
SAMEPERIODLASTYEAR ( Assets[Value] ) )
/ CALCULATE ( SUM ( Assets[Value] ),
SAMEPERIODLASTYEAR ( Assets[Value] ) )
)
E
76
You need to create a dashboard in the Power BI service to display data from a PubNub source.
What should you do?
A. Add a Microsoft SQL Server Analysis Services
(SSAS) data source that uses Connect live and
create a report. Pin the report to a dashboard.
B. Create an app workspace and publish the
workspace to a dashboard.
C. Add a Microsoft Azure SQL database data source
that uses DirectQuery and create a report. Pin the
report to a dashboard.
D. Add a custom streaming data tile to a dashboard.
D
77
Your company has a custom line-of-business application named SalesApp.
The developers of SalesApp want to push data into the Power BI service to create several visualizations.
You need to ensure that the developers can push the data from SalesApp to the Power BI service.
What should you do?
A. Go to portal.azure.com and create a web app.
B. Go to dev.powerbi.com/apps and register an
application.
C. Go to app.powerbi.com/admin-portal and click
Publish to web.
D. Go to app.powerbi.com and create an app
workspace.
B
78
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 have a query for a table named Sales. Sales has a column named CustomerID. The Data Type of CustomerID is Whole Number.
You refresh the data and find several errors. You discover that new entries in the Sales table contain nonnumeric values.
You need to ensure that nonnumeric values in the CustomerID column are set to 0.
Solution: From Query Editor, select the CustomerID column. Click Replace Errors"¦ and enter a value of 0
Does this meet the goal?
A. Yes
B. No
A
79
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 have a query for a table named Sales. Sales has a column named CustomerID. The Data Type of CustomerID is Whole Number.
You refresh the data and find several errors. You discover that new entries in the Sales table contain nonnumeric values.
You need to ensure that nonnumeric values in the CustomerID column are set to 0.
Solution: From Query Editor, open Advanced Editor and add the following query step.
#"Replace Errors"=Table.ReplaceErrorValues(#"Changed Type",{{"CustomerID,0}})
Does this meet the goal?
A. Yes
B. No
B
Correct is:
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"CustomerID", 0}})
in
#"Replaced Errors"
80
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 have a query for a table named Sales. Sales has a column named CustomerID. The Data Type of CustomerID is Whole Number.
You refresh the data and find several errors. You discover that new entries in the Sales table contain nonnumeric values.
You need to ensure that nonnumeric values in the CustomerID column are set to 0.
Solution: From Query Editor, select the CustomerID column and click Remove Errors.
Does this meet the goal?
A. Yes
B. No
B
81
```
You are creating a work schedule for a retail store.
You have the following data from a query named Schedule.
Employee; Scheduled
Ike; 1 Sunday
Ted; 1 Sunday
Jonathan; 2 Monday
Ike; 3 Tuesday
Vivek; 3 Tuesday
Margo; 4 Wednesday
Margo; 5 Thursday
Ted; 6 Firday
Jonathan; 7 Saturday
Margo; 7 Saturday
```
You need to visualize the data as shown in the following exhibit.
Matrix of Employess and Days with a mark if they are Scheduled
You add a matrix visualization, and then you add Employee to the rows and Scheduled to columns.
Which DAX formula should you use to create the measure that will display the checkboxes? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or mot 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.
Select and Place:
Options for VALUE:
COUNTA, COUNTROWS, COUNTIX, LOWER, UNICHAR, UPPER
Answer AREA
Schedule Display = IF(VALUE(Schedule>0,
VALUE(9635), "")
Schedule Display = IF(COUNTROWS(Schedule>0,
| UNICHAR(9635), "")
82
DRAG DROP -
You have a property named FactInternetSales used by several Power BI reports. The query is shown in the exhibit. (Click the Exhibit button.)
PowerQuerry with Table FactInternetSales
and Table.RemoveColumns(dbo_FactInternetSales,
You plan to create a bar chart showing the count of sales by year that have a SalesAmount greater than $1,000.
You need to create a measure that will be used in the bar chart.
How should you complete the DAX formula? 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.
Select and Place:
Options for VALUE:
CALCULATE, COUNTA, COUNTX, COUNT, COUNTROWS, FILTER
LargerSales = VALUE(VALUE('FactInternetSales', 'FactInternetSales'[SalesAmount > 1000))
LargerSales = COUNTROWS(CALCULATE('FactInternetSales', 'FactInternetSales'[SalesAmount > 1000))
83
DRAG DROP -
You have three Power BI Desktop projects named Report1.pbix, Report2.pbix, and Report3.pbix that have the following characteristics:
✑ Report1.pbix contains a custom visualization.
✑ Report2.pbix implements row level security.
✑ Report3.pbix connects to a Microsoft SQL Server database by using DirectQuery.
Which reports support Publish to Web, and which reports can be published to Power BI Report Server? To answer, drag the appropriate reports to the correct targets. Each report 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.
Select and Place:
Options for REPORT:
Report1 only, Report2 only, Report3 only, Report1 and Report2, Report1 and Report3, Report2 and Report3, All three Reports
Answer Area:
Reports that support Publish to web: REPORT
Reports that can be published to Power BI Report Server: REPORT
Reports that support Publish to web: Report1 only
Reports that can be published to Power BI Report Server: All three Reports
84
You have a Power BI model for sales data. You create a measure to calculate the year-to-date sales.
You need to compare the year-to-date sales with the previous year for the same time period.
Which DAX function should you use?
```
A. LASTDATE
B. TOTALYTD
C. SAMEPERIODLASTYEAR
D. PREVIOUSYEAR
E. DATEADD
F. DATESVTD
```
C
85
From the Home tab in Power BI Desktop, you click Enter Data and create a table named Sales that contains the following data
```
Region; Sales
Canada; 100
Canada; 900
Italy; 500
Spain; 800
US; 200
US; 1000
```
You add Region and Sales to visualization and the visualization displays the following data.
```
Region; Sales
Canada; 1000
Italy; 500
Spain; 800
US; 1200
```
```
What causes the visualization to display four rows of data instead of six?
A. the Data Category of Region
B. the Default Summarization on Region
C. the Default Summarization on Sales
D. the Data Category of Sales
```
C
86
DRAG DROP -
You have a Power BI model that contains tables named Sales and Date. Sales contains four columns named SalesAmount, OrderDate, SalesPerson, and
OrderID.
You need to create a measure to calculate the last 12 months of sales. You must start from the last date a sale was made and ignore any filters set on the report.
How should you complete the DAX formula? 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.
Select and Place:
Options for VALUE:
ALLEXCEPT, DATEDIFF, LASTNONBLANK, DATEADD, LASTDATE
Answer Area:
Last12monthSales = Var varlast12m = CALCULATE(VALUE(VALUE(Sales[OrderDate], SUM(Sales[SalesAmount])), -12 , MONTH)
CALCULATE(
DATEADD(
LASTNONBLANK(Sales[OrderDate];
SUM(Sales[SalesAmount])); -12;MONTH))
```
return
CALCULATE(SUMX(FILTER(Sales;Sales[OrderDate]>varlast12m);
Sales[SalesAmount]))
```
87
You have a table named Sales. A sample of the data in Sales is shown in the following table.
SalesOrderID; ProductName; OrderQty; OrderDate; UnitPrice; TotalPrice
71774; Bike; 1; May 1, 2017; 356.898; 356.898
71774; Car; 1; May 1, 2017; 356.898; 356.898
71775; Train; 1; May 2, 2017; 1430.442; 1430.442
71775; Puzzle; 3; May 2, 2017; 63.9; 191.7
71775; Skateboard; 4; May 3, 2017; 32.394; 129.576
71776; Doll; 1; May 4, 2017; 63.9; 63.9
You create a stacked column chart visualization that displays ProductName by Date.
You discover that the axis for the visualization displays all the individual dates.
You need to ensure that the visualization displays ProductName by year and that you can drill down to see ProductName by week and day.
What should you do first?
A. Create a new table that has columns for the date,
year, week, and day.
B. Create a new hierarchy in the Sales table.
C. Format the visualization and set the type of the X-
Axis to Categorical.
D. Configure a visual filter for the Date column that
uses an advanced filter.
A
88
You need to create a custom visualization for Power BI.
What should you install first?
A. jQuery
B. Node.js
C. Microsoft Azure PowerShell
D. Microsoft.NET
B
89
DRAG DROP -
You have the following two tables:
✑ Subscriber (SubscriberID, EnrollmentDate, ServicePlan)
✑ Date (Date, Month, Week, Year)
There is a relationship between Subscriber[EnrollmentDate] and Date [Date].
You plan to create a KPI for the number of subscribers enrolled in the current year.
You need to create a goal that is five percent more than the number of subscribers enrolled during the previous calendar year.
How should you complete the DAX formula? 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.
Select and Place:
```
Options for VALUE:
PARALLELPERIOD
CALCULATE
COUNT
DATESYTD
PREVIOUSYEAR
SMX
TOTALYTD
```
Answer Area:
Goal = VALUE(VALUE('Subscriber'[SubscriberID]), VALUE('Date'[Date]))*1.05
Goal = CALCULATE(COUNT('Subscriber'[SubscriberID]), PREVIOUSYEAR('Date'[Date]))*1.05
90
You have a Power BI report that is configured to use row-level security (RLS).
You have the following roles:
✑ A manager role that limits managers to see only the sales data from the stores they manage
✑ A region role that limits users to see only the data from their respective region
You plan to use Power BI Embedded to embed the report into an application. The application will authenticate the users.
You need to ensure that RLS is enforced when accessing the embedded report.
What should you do?
A. From dev.powerbi.com/apps, register the new application and enable the Read All Reports API access.
B. In the access token for the application, include the user name and the role name.
C. From dev.powerbi.com/apps, register the new application and enable the Read All Groups API access.
D. In the access token for the application, include the report URL and the Microsoft Azure Active Directory domain name.
B
91
You are creating a report in Power BI Desktop that has two visualizations on a page as shown in the following exhibit.
Revenue by Year and Manufactor visual
Units by Country visual
You need to ensure that when you click the bar of a country, only the values for that country are shown on the Revenue by Year and Manufacturer chart.
A. Click the Revenue by Year and Manufacturer chart.
On the Format tab, click Edit Interactions. On the
Units by Country chart, click Filter.
B. Click the Revenue by Year and Manufacturer chart.
On the Format tab, click Edit Interactions. On the
Units by Country chart, click Highlight.
C. Click the Units by Country chart. On the Format tab,
click Edit Interactions. On the Revenue by Year and
Manufacturer chart, click Filter.
D. Click the Units by Country chart. On the Format tab,
click Edit Interactions. On the Revenue by Year and
Manufacturer chart, click Highlight.
C
92
Your company has a security policy stating that proprietary data must not be transferred over the Internet.
During a security audit, auditors discover that executives use the Power BI service for reporting.
You need to recommend a solution to ensure that the company adheres to the security policy.
What should you include in the recommendation?
A. Microsoft SQL Server column encryption
B. Microsoft Azure ExpressRoute
C. a site-to-site VPN to Microsoft Azure
D. the on-premises gateway for Power BI
B
93
You have a Power BI Desktop project that uses DirectQuery to access an on-premises Microsoft SQL Server database.
From Power BI Desktop, you can query the database.
When you publish the Power BI Desktop project to the Power BI service, the visualizations cannot display the data.
What should you do to resolve the issue?
A. Locate the published dataset for the project in the
Power BI service and configure the data source
credentials.
B. Install the on-premises data gateway (personal
mode) and republish the project.
C. Install the on-premises data gateway and configure
a data source.
D. Configure a Microsoft Azure ExpressRoute
connection between the on-premises network and
the Power BI service.
C
94
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Order_date, Order_amount, Customer_ID, Order_ship_date, Store_ID)
Customer(Customer_ID, First_name, Last_name, Customer_photo)
Date(DateID, Date_name, Month, Week, Year)
Monthly_returns(MonthID, Total_returns, StoreID)
Store(StoreID, Name, City, Sales_target)
The following columns contain date information:
✑ Date[Month] in the mmyyyy format
✑ Date[Date_ID] in the ddmmyyyy format
✑ Date[Date_name] in the mm/dd/yyyy format
✑ Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You need to create a chart that displays a sum of Order[Order_amount] by month for the Order_ship_date column and the Order_date column.
How should you model the data?
A. Create a one-to-many relationship from
Date[Date_ID] to Order[Order_date] and another
relationship from Date[Date_ID] to
Monthly_returns[Date_ID].
B. Add a second Date table named Ship_date to the
model. Create a many-to-many relationship from
Date[Date_ID] to Order[Order_date] and many-to-
many relationship from Ship_date[Date_ID] to
Order[Order_ship_date].
C. Add a second Date table named Ship_date to the
model. Create a one-to-many relationship from
Date[Date_ID] to Order[Order_date] and a one-to-
many relationship from Ship_Date[Date_ID] to
Order[Order_ship_date].
D. Create a one-to-many relationship from
Date[Date_ID] to Order[Order_date] and another
relationship from Date[Date_ID] to
Order[Order_ship_date].
D
95
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Order_date, Order_amount, Customer_ID, Order_ship_date, Store_ID)
Customer(Customer_ID, First_name, Last_name, Customer_photo)
Date(DateID, Date_name, Month, Week, Year)
Monthly_returns(MonthID, Total_returns, StoreID)
The following columns contain date information:
✑ Date[Month] in the mmyyyy format
✑ Date[Date_ID] in the ddmmyyyy format
✑ Date[Date_name] in the mm/dd/yyyy format
✑ Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You are modeling the data in Power BI.
You need to import only a sample of the data from the Order table.
What are two possible ways to achieve the goal? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. From Query Editor, create a custom column that
uses a custom column formula.
B. From Query Editor, add a SELECT statement that
uses a WHERE clause to the source definition.
C. In the Power BI model, create a calculated table.
D. From Query Editor, filter the table by Order_date.
E. From Query Editor, create a column by using
Column From Examples.
B, D
96
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Order_date, Order_amount, Customer_ID, Order_ship_date, Store_ID)
Customer(Customer_ID, First_name, Last_name, Customer_photo)
Date(DateID, Date_name, Month, Week, Year)
Monthly_returns(MonthID, Total_returns, StoreID)
The following columns contain date information:
✑ Date[Month] in the mmyyyy format
✑ Date[Date_ID] in the ddmmyyyy format
✑ Date[Date_name] in the mm/dd/yyyy format
✑ Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You are modifying the model to report on the number of orders.
You need to calculate the number of orders.
What should you do?
A. Create a calculated measure that uses the
COUNTA(Order_ID) DAX formula.
B. Create a calculated column that uses the
COUNTA(Order_ID) DAX formula.
C. Create a calculated column that uses the
SUM(Order_ID) DAX formula.
D. Create a calculated measure that uses the
SUM(Order_ID) DAX formula.
A
97
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Order_date, Order_amount, Customer_ID, Order_ship_date, Store_ID)
Customer(Customer_ID, First_name, Last_name, Customer_photo)
Date(DateID, Date_name, Month, Week, Year)
Monthly_returns(MonthID, Total_returns, StoreID)
Store(StoreID, Name, Citty, Sales_target)
The following columns contain date information:
✑ Date[Month] in the mmyyyy format
✑ Date[Date_ID] in the ddmmyyyy format
✑ Date[Date_name] in the mm/dd/yyyy format
✑ Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You plan to create a chart that displays total Order[Order_amount] by Store[Name].
You need to modify the model to ensure that you can create the chart.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Create a relationship between the Order table and
the Store table.
B. To the Order table, add a measure that uses the
COUNTA('Order'[Order_ID]) DAX formula.
C. To the Order table, add a column that uses the
RELATED('Store'[Store_ID]) DAX formula.
D. To the Order table, add a measure that uses the
COUNT('Order'[Order_amount]) DAX formula.
A, B
98
You plan to create a dashboard in the Power BI service that retrieves data from a Microsoft SQL Server database. The dashboard will be shared between the users in your organization.
You need to ensure that the users will see the current data when they view the dashboard.
How should you configure the connection to the data source?
A. Deploy an on-premises data gateway. Connect to
the data by using the Import Data Connectivity
mode.
B. Deploy an on-premises data gateway. Connect to
the data by using the DirectQuery Data Connectivity
mode.
C. Deploy an on-premises data gateway (personal
mode). Connect to the data by using the Import
Data Connectivity mode.
D. Deploy an on-premises data gateway (personal
mode). Connect to the data by using the
DirectQuery Data Connectivity mode.
B
99
You plan to use Power BI Desktop optimized for Power BI Report Server to create a report. The report will be published to Power BI Report Server.
You need to ensure that all the visualization in the report can be consumed by users.
Which three types of visualizations should you include in the report? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
```
A. bubble maps
B. custom visuals
C. R visuals
D. breadcrumbs
E. funnel charts
```
A, B, E
100
You plan to create a dashboard in the Power BI service that will retrieve data from a tabular database in Microsoft SQL Server Analysis Services (SSAS). The dashboard will be shared between the users in your organization.
The Analysis Services database has a DirectQuery connection to the SQL Server database that contains the source data.
You need to ensure that the users will see the current data when they view the dashboard.
How should you configure the connection to the data source?
A. Deploy an on-premises data gateway. Connect to
the data by using the Connect live option.
B. Deploy an on-premises data gateway. Connect to
the data by using the DirectQuery Data Connectivity
mode.
C. Deploy an on-premises data gateway (personal
mode). Connect to the data by using the Connect
live option.
D. Deploy an on-premises data gateway (personal
mode). Connect to the data by using the
DirectQuery Data Connectivity mode.
A
101
HOTSPOT -
You open powerbi.com as 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.
Hot Area:
```
A tenant Administrator created a data classification that has a shorthand of
A. Customers
B. Insurance
C. Insurance Data
D. Sale
```
```
The Dashboard uses a dataset named
A. Customers
B. Insurance
C. Insurance Data
D. Sale
```
D, B
102
DRAG DROP -
You create a new app workspace. You add a user named User1 as a member of the workspace. User1 can edit content.
You plan to create a report in an app workspace that uses data from a Microsoft Azure SQL database.
You need to create the report. The solution must ensure that User1 can edit the report from Power BI Desktop and from powerbi.com.
Which three actions should you perform in sequence? To answer, move the appropriate action from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
1. From Power BI Desktop, publish the report to the
Power BI Service
2. From powerbi.com, add a dataset
3. From powerbi.com, create a report
4. From powerbi.com, publish the report to the web.
5. From Power BI Desktop, create a report.
6. From Power BI Desktop, add a data Source
6. -> 5. -> 1.
103
HOTSPOT -
You have the following tables.
Sales(SalesOrderID, SalesDate, OrderQty, UnitPrice, SalesAmount, CustomerID)
Customers(CustomerID, CustomerName, Phone, Email)
You need to create a new table that displays the top 10 customers by the total of SalesAmount.
How should you complete the DAX formula? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Options for VALUE:
CALCULATE, FILTER, TOPN, RANKX, VALUES
Top Customers = VALUE (SUM(Sales[Profit]),
VALUE(10,Customer,SUM(Sales[Profit])))
Top Customers = CALCULATE(SUM(Sales[Profit]),
| TOPN(10,Customer,SUM(Sales[Profit])))
104
You plan to use Power BI Desktop to create a report. The report will consume data from an on-premises tabular database named SalesDB in Microsoft SQL
Server Analysis Services (SSAS). The report will be published to the Power BI service.
You need to ensure that the report published to the Power BI service will access the current data in SalesDB.
What should you do?
A. Deploy an on-premises data gateway and configure
the connection to SalesDB to use the Connect live
option.
B. Deploy an on-premises data gateway and configure
the connection to SalesDB to use the Import Data
Connectivity mode.
C. Deploy an on-premises data gateway (personal
mode) and configure the connection to SalesDB to
use the DirectQuery Data Connectivity mode.
D. Deploy an on-premises data gateway and configure
the connection to SalesDB to use the DirectQuery
Data Connectivity mode.
A
105
You plan to join a fact table named ActivityLog to a Date dimension named ActivityDate. The date value in ActivityLog is a datetime column named ActivityStart.
The date value in ActivityDate is a number column named DateID. DateID is in the YYYYMMDD format.
What should you do in the model before you create the relationship?
A. Change the Data Type of ActivityStart to Date.
B. Create a measure in ActivityLog that uses the
FORMAT DAX function.
C. Change the Data Type of DateID to Date.
D. Create a calculated column in ActivityLog that uses
the FORMAT DAX function.
D
106
You have a table in Power BI Desktop as shown in the following exhibit.
Table(Id, Key, Value)
With tow diffrent values for the same Id/Key combination.
You pivot the columns as shown in the following exhibit.
Table(Id, Student, Calss, Score)
You need to resolve the error in row 3. The solution must preserve all the data.
What should you do?
A. Change the Data Type of the Value column.
B. Select the Key column, and then click Remove
Duplicates.
C. Change the Aggregate Value Function of the pivot.
D. Select the Score column, and then click Remove
Errors.
C
107
HOTSPOT -
You have a query that uses a Microsoft Excel data source. The data source contains the following table.
GeoCode, CustomerCount, 2014, 2015, 2016, 2017
You need the data to appear as shown in the following table.
GeoCode, CustomerCount, Attributem Value
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Columns to select:
A. GeoCode only
B. GeoCode and CustomerCount
C. 2014, 2015, 2016 and 2017
Command to use:
A. Pivot Column
B. Reverse Rows
C. Unpivot Columns
B, C
108
You attempt to publish a Microsoft Excel file to Power BI, and you receive the error message shown in the exhibit. (Click the Exhibit button.)
ExcelSheet with Error: We could't publish to Power BI
The file is in c:\data\.
You need to ensure that you can publish the file to Power BI.
What should you do first?
A. Save the file in a Microsoft SharePoint document library.
B. Decrypt the workbook.
C. Add a digital signature to the workbook.
D. Set the file attributes to read-only.
B
109
You have two tables named Customer and Orders. A sample of the Data in Customer is shown in the following table.
Customer(CustomerID, CustomerName)
A sample of the data in Orders is shown in the following table.
Orders(OrderID, CustomerID, OrderDate, OrderAmount)
You need to create the following new table.
Orders(CustomerID, CustomerName, OrderID, OrderDate, OrderAmount)
```
You must use Customer as the first table.
Which join kind should you use?
A. Right Anti
B. Right Outer
C. Left Anti
D. Left Outer
E. Inner
```
D
110
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.
Your company has 1,000 users in a Microsoft Office 365 subscription.
A Power BI administrator named Admin1 creates 20 dashboards and shares them with 50 users.
You discover that a user named User1 can access all the dashboards.
You need to prevent User1 from accessing all the dashboards.
Solution: From the properties of each dashboard, you modify the Share settings.
Does this meet the goal?
A. Yes
B. No
A
111
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.
Your company has 1,000 users in a Microsoft Office 365 subscription.
A Power BI administrator named Admin1 creates 20 dashboards and shares them with 50 users.
You discover that a user named User1 can access all the dashboards.
You need to prevent User1 from accessing all the dashboards.
Solution: From the Power BI Admin portal, you modify the Dashboard settings.
Does this meet the goal?
A. Yes
B. No
B
112
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.
Your company has 1,000 users in a Microsoft Office 365 subscription.
A Power BI administrator named Admin1 creates 20 dashboards and shares them with 50 users.
You discover that a user named User1 can access all the dashboards.
You need to prevent User1 from accessing all the dashboards.
Solution: From the Office 365 Admin center, you remove the Power BI license from User1.
Does this meet the goal?
A. Yes
B. No
B
113
HOTSPOT -
You have the datasets shown in the following graphic.
Dataset1 with Streaming API Access
Dataset2 with Hybrid API Access
Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.
Note: Each selection is worth one point.
Hot Area:
When designing a Dashboard that uses Dataset1, you can use:
A. only report visualizations
B. only streaming data tiles
C. both reporting visualizastions and Streaming data
tiles
When designing a dashboard that uses Dataset2, you can use:
A. only report visualizations
B. only streaming data tiles
C. both reporting visualizastions and Streaming data
tiles
B, C
114
You have a Power BI model for sales data. You create a measure to calculate the year-to-date sales.
You need to compare the year-to-date sales with the previous year for the same time period.
Which DAX function should you use?
A. DATEADD
B. TOTALYTD
C. DATESYTD
D. ENDOFYEAR
A
115
DRAG DROP -
You are configuring the relationships between the following tables.
InsurancePolicy(PolicyID, AccountID, Policy_cost, Date)
Account(AccountID, AccountName)
BridgeAccount(AccountID, CustomerID)
Customer(CustomerID, CustomerName)
A customer can have multiple accounts. An account can only be associated to one customer. Each account is associated to only one insurance policy.
You need to configure the relationships between the tables to ensure that you can create a report displaying customers and their associated insurance policies.
How should you configure each relationship? To answer, drag the appropriate cardinalities to the correct relationships. 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.
Select and Place:
Cardinalities:
Many-to-one
One-to-many
One-to-one
Answer Area:
Relationship from InsurancePolicy to Account: A
Relationship from Account to BridgeAccount: B
Relationship from Customer to BridgeAccount: C
A: One-to-one
B: One-to-one
C: One-to-many
116
You have a Power BI Desktop project that has the model shown in the exhibit. (Click the Exhibit tab.)
Priducts 1 --->---* Sales *--->---1 Customers
Customer Count is a measure that uses the CountRows function to calculate the number of customers.
You create a table visualization that displays ProductID, Product, and Customer Count.
When you view the table, you discover that Customer Count always displays the total number of customers instead of the number of customers who purchased the product.
You need to ensure that the table visualization displays the number of customers who purchased each product.
What should you do?
A. Modify the table relationship between the
Customers table and the Sales table to use a Cross
filter direction of Both.
B. Modify the Customer Count measure to use the
COUNT function.
C. Modify the Customer Count measure to use the
COUNTX function.
D. Modify the table relationship between the Products
table and the Sales table to use a Cross filter
direction of Both.
A
117
DRAG DROP -
You have sales data in a spreadsheet named Sales.xlsx.
You need to provide a detailed sales report to several managers.
From the Power BI service, you create an app workspace named SalesWorkspace.
Which three actions should you perform in sequence next? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Select and Place:
Actions:
1. From the dataset, click Security
2. Add a dataset
3. Create a new report
4. Create a Dashboard
5. Publish the app workspace
2. -> 3. -> 5.
118
DRAG DROP -
You need to create a measure named YTDPreviousSales that will be used in a table visualization. YTDPreviousSales must show the year-to-date (YTD) sales of the previous year for the same month. A sample of the desired data is shown in the following table.
```
SalesAmount; YTDPreviousSales; Year; Month
400; 515; 2017; Januar
500; 1025; 2017; Februar
480; 1505; 2017; March
415; 900; 2018; Januar
```
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.
Select and Place:
```
Options for VALUES:
CALCULATE
CALENDAR
DATEADD
DATEDIFF
FILTER
TOTALYTD
YTD
```
Answer Area:
YTDPreviousSales = VALUE (SUM(FactInternetSAles[SalesAmount]), VALUE(DimDate[Date], -12, MONTH))
YTDPreviousSales = CALCULATE (SUM(FactInternetSAles[SalesAmount]), DATEADD(DimDate[Date], -12, MONTH))
119
You create a dashboard that displays the results of a customer satisfaction survey.
You need to embed a tweet from your company's Twitter feed into the dashboard.
What should you do?
A. Edit the report and import a visualization from the
marketplace. Pin the visualization to the dashboard.
B. Edit the report and import a visualization from a file.
Pin the visualization to the dashboard.
C. To the dashboard, add a tile that uses a
web Content source.
D. To the dashboard, add a tile that uses a PubNub
content source.
C
120
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Oerder_date, Oerder_amount, CustomerID, Oder_ship_date)
Customer(CustomerID, First_name, Last_name, Customer_photo)
Date(Date_ID, Date_name, Month, Week, Year)
Monthly_returns(Month_ID, Total_returns, Store_ID)
Store(Store_ID, Name, City, Sales_target)
The following columns contain date information:
- Date[Month] in the mmyyyy format
- Date[Date_ID] in the ddmmyyyy format
- Date[Date_name] in the mm/dd/yyyy format
- Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You need to create a relationship between the Monthly_returns table and Date[Date_ID].
What should you do before you create the relationship?
A. In the Date table, create a new calculated column
named Month_ID that uses the yyyydd format.
B. In the Monthly_returns table, create a new
calculated column named Date_ID that uses the
ddmmyyyy format.
C. To the Order table, add a calculated column that
uses the RELATED(Monthly_returns[Month_ID]) DAX
formula.
D. To the Date table, add a calculated column that
uses the RELATED(Monthly_returns[Month_ID]) DAX
formula.
B
121
DRAG DROP -
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Oerder_date, Oerder_amount, CustomerID, Oder_ship_date)
Customer(CustomerID, First_name, Last_name, Customer_photo)
Date(Date_ID, Date_name, Month, Week, Year)
Monthly_returns(Month_ID, Total_returns, Store_ID)
Store(Store_ID, Name, City, Sales_target)
The following columns contain date information:
- Date[Month] in the mmyyyy format
- Date[Date_ID] in the ddmmyyyy format
- Date[Date_name] in the mm/dd/yyyy format
- Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You need to display the month as a three-letter abbreviation, followed by the year, such as jan2017.
You add a calculated column in Power BI.
Which DAX formula should you use for the calculated column? 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.
Select and Place:
```
VALUES:
CONCATENATEX
CONCATENATE
Combin
MMM
CombinA
M
MMMM
MM
```
Answer Area:
Column = VALUE (FORMAT(MONTH([Date_name]), "VALUE"), (FORMAT(YEAR([Date_name]), "yyyy"))
Column = CONCATENATE (FORMAT(MONTH([Date_name]), "MMM"), (FORMAT(YEAR([Date_name]), "yyyy"))
122
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
You have a Microsoft SQL Server database that contains the following tables.
Order(OrderID, Oerder_date, Oerder_amount, CustomerID, Oder_ship_date)
Customer(CustomerID, First_name, Last_name, Customer_photo)
Date(Date_ID, Date_name, Month, Week, Year)
Monthly_returns(Month_ID, Total_returns, Store_ID)
Store(Store_ID, Name, City, Sales_target)
The following columns contain date information:
- Date[Month] in the mmyyyy format
- Date[Date_ID] in the ddmmyyyy format
- Date[Date_name] in the mm/dd/yyyy format
- Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows.
The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables.
You plan to use Power BI Desktop to create an analytics solution for the data.
You need to create a relationship between the Order table and the Store table on the Store_ID column.
What should you do before you create the relationship?
```
A. In the Order table query, use the
Table.TrasformRows function.
B. In the Store table query, use the Table.TrasformRows
function.
C. In the Store table query, use the
Table.TrasformColumnTypes function.
D. In the Order table query, use the
Table.TrasformColumnTypes function.
```
E. No Action needed, but datatypes matter when craeting a relationship. So make shure Store_ID in both tables is in the same datatype
123
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 have a user named User1. User1 is a member of a security group named Contoso PowerBI.
User1 has access to a workspace named Contoso Workspace.
You need to prevent User1 from exporting data from the visualizations in Contoso Workspace.
Solution: From the Power BI Admin portal, you modify the Tenant settings.
Does this meet the goal?
A. Yes
B. No
A (but not best way)
124
HOTSPOT -
You have a Power BI model that has the following tables:
✑ Product (Product_id, Product_Name)
✑ Sales (Order_id, Order_Date, Product_id,
Salesperson_id, Sales_Amount)
✑ Salesperson (Salesperson_id, Salesperson_name,
address)
You plan to create the following measure.
Measure1 = DISTINCTCOUNT(Sales[ProductID])
You need to create the following relationships:
✑ Sales to Product
✑ Sales to Salesperson
The solution must ensure that you can use Measure1 to display the count of products sold by each salesperson.
How should you configure the relationships? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Cardinality:
A. Many to One (*:1)
B. One to Many (1:*)
C. One to One (1:1)
Cross filter direction
A. Both
B. Single
✑ Sales to Product
A, B
✑ Sales to Salesperson
A, A
125
HOTSPOT -
You have a report in Power BI Desktop as shown in the following exhibit.
PBI Report page with radial gauge chart
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.
Hot Area:
The Goal is set by using:
A. a calculated measure
B. a DAX Formula
C. the Format settings
To configure the visualization to Display TotalPrice for the Territory of Canada always, you must add the Territory column to:
A. the Tooltips field
B. the Values field
C. the Visual Level filters field
C, C
126
You have the following table named Location
```
GeoCode; CustomerCount
CA;9530
AR;540
MA; 2300
SD; 1200
PA; 340
NC; 890
```
The GeoCode column represents the country where each customer is located.
You create a map visualization as shown in the exhibit.
UnitedStatesMap.jpg
You need to ensure that the map displays the country locations.
What should you do?
A. Replace the values in the GeoCode column with
postal codes or zip codes.
B. Change the name of the GeoCode column to
Country.
C. Change the name of the Location table to Country.
D. Change the Default Summarization of the GeoCode
column.
E. Add a Geoportal column to the Location table.
F. Change the Data Type of the GeoCode column.
F
127
DRAG DROP -
You have a Power BI model that contains a table named Sales. Sales has the following three measures:
✑ A measure named Total Sales Last Year that displays the sales from the previous calendar year. The current value is 32.89 million.
✑ A measure named Total Sales This Year that displays the sales from the current calendar year. The current value is 11.69 million.
✑ A measure named Total Sales Difference that uses a DAX formula of Sales[Last Year] "" Sales[This Year].
You need to create the following visualization.
RadialGaugeChart.jpg
How should you configure the visualization? To answer, drag the appropriate measures to the correct fields. 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.
Select and Place:
MESURES:
A. Total Sales Difference
B. Total Sales Last Year
C. Total Sales This Year
Answer Area:
Value:
Maximum value:
Target value:
Value: C
Maximum value: B
Target value: B
128
DRAG DROP -
You have a column named phone_number. The values in the columns are in one of the following formats:
✑ 999-999-9999x123
✑ 1-999-999-9999x232
✑ +1-999-999-9999x667
The values after x in the phone_number column indicate the phone extension.
You need to create a custom column in Query Editor that contains only the phone extensions.
How should you complete the query? 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.
Select and Place:
```
Options for VALUE:
AfterDelimiter
RelativePosition
PositionOf
Range
RemoveRange
TrimEnd
PositionOfAny
```
Answer Area:
Text.VALUE([phone_number], "x", {0, VALUE.FromEnd})
Text.AfterDelimiter([phone_number], "x", {0, RelativePosition.FromEnd})
129
HOTSPOT -
You are creating reports in Power BI Desktop. The model has the following tables.
Order (Order_date, Order_amount, Customer_ID)
Customer ( Customer_ID, Full_name, Customer_Photo)
There is a relationship between the tables.
You plan to publish a report to the Power BI service that displays Order_amount by Order_date by Full_name.
You need to ensure that only the columns required for the report appear in Report View. The solution must minimize the size of the dataset that is published.
How should you configure the columns in Power BI Desktop? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Customer_ID:
A. From Querry Editor, select the column and click
Remove Columns.
B. From Querry Editor, select the column and click
Remove Duplicates
C. From Querry Editor, select the column and click
Remove Other Columns
D. From the model, select the column and click
Hide
Customer_Photo:
A. From Querry Editor, select the column and click
Remove Columns.
B. From Querry Editor, select the column and click
Remove Duplicates
C. From Querry Editor, select the column and click
Remove Other Columns
D. From the model, select the column and click
Hide
D, A
130
HOTSPOT -
You have a table that contains a column named Phone. The following is a sample of the data in the Phone column.
```
436-555-0160
385-555-0140
1 (11) 500 555-0122
138-55-0156
.
.
.
```
How should you complete the Query Editor formula? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
= Table.AddColumn(#"Previous Step", "Custom", each Text. [Insert, Remove Replace or ReplaceRange]
(Text.[At, End, Middle or Range] ([Phone],12), " ", "-"))
= Table.AddColumn(#"Previous Step", "Custom", each Text. Replace(Text.End([Phone],12), " ", "-"))
131
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 have a query for a table named Sales. Sales has a column named CustomerID. The Data Type of CustomerID is Whole Number.
You refresh the data and find several errors. You discover that new entries in the Sales table contain nonnumeric values.
You need to ensure that nonnumeric values in the CustomerID column are set to 0.
Solution: From Query Editor, select the CustomerID column and click Replace Values....
Does this meet the goal?
A. Yes
B. No
B Use "Replace Errors"
132
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 have a Microsoft Excel workbook that is saved to Microsoft SharePoint Online. The workbook contains several Power View sheets.
You need to recreate the Power View sheets as reports in the Power BI service.
Solution: Copy the workbook to Microsoft OneDrive for Business. From Excel, click Publish to Power BI, and the click Upload.
Does this meet the goal?
A. Yes
B. No
B, last click is Export not Upload
133
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 have a Microsoft Excel workbook that is saved to Microsoft SharePoint Online. The workbook contains several Power View sheets.
You need to recreate the Power View sheets as reports in the Power BI service.
Solution: From the Power BI service, get the data from SharePoint Online, and then click Connect.
Does this meet the goal?
A. Yes
B. No
B Need to click "Import" not "Connect"
134
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 have a Power BI model that contains two tables named Sales and Date. Sales contains four columns named TotalCost, DueDate, ShipDate, and OrderDate.
Date contains one column named Date.
The tables have the following relationships:
✑ Sales[DueDate] and Date[Date]
✑ Sales[ShipDate] and Date[Date]
✑ Sales[OrderDate] and Date[Date]
The active relationship is on Sales[DueDate].
You need to create measures to count the number of orders by [ShipDate] and the orders by [OrderDate]. You must meet the goal without duplicating data or loading additional data.
Solution: You create measures that use the CALCULATE, COUNT, and USERELATIONSHIP DAX functions.
Does this meet the goal?
A. Yes
B. No
A
135
You manage a Power BI model has a table named Sales.
You need to ensure that a sales team can view only data that has a CountryRegionName value of United States and a ProductCategory value of Clothing.
What should you do from Power BI Desktop?
A. From Power BI Desktop, create a new role that has
the following filter. [countryRegionName]= "United
States" && [ProductCategory]= "Clothing"
B. Add the following filters in Query Editor.
CountryRegionName is United States
ProductCategory is Clothing
C. From Power BI Desktop, create a new role that has
the following filters. [CountryRegionName]= "United
States"
D. Add the following filters to a report.
CountryRegionName is United Sates
A
136
In the Power BI service, you create an app workplace that contains several dashboards.
You need to provide a user named user1@contoso.com with the ability to edit and publish dashboards.
What should you do?
A. From the app workplace, click Update app, and then
configure the Access settings.
B. Share the dashboard, and then modify the Access
settings of the dashboard.
C. Configure security for the dataset used by the app.
D. Modify the members of the app workspace.
D
137
You plan to create a dashboard in the Power BI service that retrieves data from a Microsoft SQL Server database. The dashboard will be shared between the users in your organization.
You need to ensure that the users will see the current data when they view the dashboard.
How should you configure the connection to the data source?
A. Deploy an on-premises data gateway (personal
mode). Import the data by using the Import Data
Connectivity mode.
B. Deploy an on-premises data gateway. Import the
data by using the Import Data Connectivity mode.
C. Deploy an on-premises data gateway. Import the
data by using the DirectQuery Data Connectivity
mode.
D. Deploy an on-premises data gateway (personal
mode). Import the data by using the DirectQuery
Data Connectivity mode.
C
138
You have an on-premises Power BI Report Server.
You plan to create a report in Power BI Desktop and publish the report to the report server.
Which data source should the report use?
```
A. Microsoft Azure SQL Database
B. a Microsoft SQL Server database
C. a Microsoft SQL Server Analysis Services (SSAS)
database
D. Microsoft Excel
```
C (but all four data Sources are supported)
139
You have an app workspace named Retail Analysis in the Power BI service.
You need manage the members that have access to the app workspace.
What should you do?
A. From the Power BI Admin portal, click Usage
metrics.
B. From the Office 365 Admin center, click Users.
C. From the Office 365 Admin center, click Groups.
D. From the Power BI Admin portal, click Tenant
settings.
C
140
You plan to use Power BI Desktop to create a report. The report will consume data from an on-premises tabular named SalesDB in Microsoft SQL Server Analysis
Services (SSAS). The report will be published to the Power BI service.
You need to ensure that the report published to the Power BI service will access the current data in SalesDB.
What should you do?
A. Deploy an on-premises data gateway and configure
the connection to SalesDB to use the Import Data
Connectivity mode.
B. Deploy an on-premises data gateway and configure
the connection to SalesDB to use the Connect live
option.
C. Deploy an on-premises data gateway (personal
mode) and configure to SalesDB to use the
DirectQuery Data Connectivity mode.
D. Deploy an on-premises data gateway and configure
the connection to SalesDB to use the DirectQuery
Data Connectivity mode.
B
141
You plan to use Power BI Embedded to deliver reports in a web application.
You need to ensure that the reports display live data.
Which data source you should use?
A. Microsoft Azure Data Lake Store
B. Microsoft Azure Table Storage
C. Microsoft Azure HDInsight
D. Microsoft Azure SQL Database
D
142
You have a Power BI model that contains the following tables:
✑ Sales (Sales_ID, DateID, sales_amount)
✑ Date (DateID, Date, Month, week, Year)
The tables have a relationship. Date is marked as a date table in the Power BI model.
You need to create a measure to calculate the sales for the last 12 months.
Which DAX formula should you use?
A. CALCULATEx(SUM(sales[sales_amount])
DATESYTD (‘Date’ [Date]))
B. CALCULATE(SUM(sales[sales_amount]),
SAMEPERIODLASTYEAR (‘Date’ [Date]))
C. SUM(sales[sales_amount])-
CALCULATE(SUM(sales[sales_amount]),
SAMEPERIODLASTYEAR(‘Date’[Date]))
D. SUM(sales[sales_amount])-CALCULATE
(SUM(sales[sales_amount]),
DATESYTD(‘Date’[Date]))
B
143
You have a Power BI report in an app workspace.
You plan to embed a report from the app workspace into a line-of-business application by using Power BI Embedded.
Which information should you provide to the application developers?
A. The application token and the report URL
B. The report URL and a user name
C. The app workspace name and the access key
D. The access key and the report ID
A
144
You have a power BI model that contains the following tables:
✑ Assets( AssetsID, AssetName, Purchase_DateID, Value)
✑ Date( DateID, Date, Month, Week, Year)
The tables have relationship. Date is marked as a date table in the Power BI model.
You need to create a measure to calculate the percentage that the total assets value increased since one year ago.
Which DAX formula should you use?
```
A. (sum(Assets[Value]) –
CALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR(‘Date’[Date])))
/CALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR (‘Date’ [Date]))
B. CALCULATEx(sum(Assets[Value]) DATESYTD
(Date[Date]))/ sum(Assets[Value])
C. sum(Assets[Value])-CALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR (Date [Date]))
D. CALCULATE(sum(Assets[Value]),
SAMEPERIODLASTYEAR (Date [Date]))/
```
A (fehler beim Übertragen der Antworten?)
145
You have a Power BI model for sales data.
You need to create a measure to calculate the year-to-date sales and to compare those sales to the previous year for the same time period.
Which DAX function should you use?
A. PARALLELPERIOD
B. SAMEPERIODLAST YEAR
C. DATESYTD
D. PREVIOUSYEAR
C or A
146
The following table contains a Parent-Child hierarchy on the columns: Employee Key and Parent Employee Key. From the table you can see that employee 112 has no parent defined, employee 14 has employee 112 as manager (ParentEmployeeKey), employee 3 has employee 14 as manager and employees 11, 13, and 162 have employee 3 as manager. The above helps to understand that employee 112 has no manager above her/him and she/he is the top manager for all employees.
The PATHLENGTH Function (DAX) returns the number of levels in a given PATH(), starting at current level until the oldest or top most parent level.
What would be the PATHLENGTH value for the third row below (values 3 and 14)?
```
Employee Key; Parent Employee Key
112;
14;112
3;14
11;3
13;3
162;3
117;162
221;162
81;162
```
A. 2
B. 3
C. 4
D. 1
3
147
With Power BI Publish to web, you can easily embed interactive Power BI visualizations online, such as in blog posts, websites, through emails or social media, on any device.
You have published a visualization but unfortunately changes to the underlying data are not immediately visible to users. What might be wrong?
A. You must refresh the data manually after publishing
to the web
B. It takes up to an hour for data to refresh
C. You cannot refresh data after publishing to the web,
the visuals are static
D. The user does not have the appropriate row level
security permissions
B
148
When you share with people outside your organization, they get an email with a link to the shared dashboard. They need a Power BI Pro license, and they have to sign in to Power BI to see the dashboard.
After they sign in, they see the shared dashboard in its own browser window without the left navigation pane, not in their usual Power BI portal. They have to bookmark the link to access this dashboard in the future.
Which of the following statements is FALSE when sharing visualizations outside of your organisation?
A. They can't edit any content in this dashboard or
report
B. They can change any filters/slicers available on the
reports connected to the dashboard and save their
changes
C. People outside your organization can't see any data
if role- or row-level security is implemented on on-
premises Analysis Services tabular models
D. Only your direct recipients can see the shared
dashboard. For example, if you sent the email to
Vicki@contoso.com, only Vicki can see the
dashboard
B
149
Data has been scraped from a table on a popular retirement website. However, the Health care quality column's scores were not automatically transformed from text to numbers when Query Editor loaded the table. You thus right-clicked the column header, and selected Change Type > Whole Number to change them.
Unfortunately, the, Health care quality column contains a few ties in states' rankings, which was noted on the website by the word (tie) after their numbers. Query Editor thus reports a few errors.
What is the consequence of using the Remove Errors option (ribbon or the right-click menu option) to resolve this?
A. Removes the applied step that has errors
B. Removes the error warnings but keeps all data
intact
C. Blanks the cell values which have Errors
D. Removes any rows with errors
D
150
As part of a large HR project you are working with a dataset of company employees, both past and present. The data includes columns for EthnicGroup, PayTypeID, HireDate (the date they started work), TermDate (the date they left) and several other columns. You want to create a new Calculated Column that determines if the person was a bad hire based on a set of rules. If they were a bad hire then the result is 1, otherwise the Calculated Column displays a zero.
The bad hire rule is:
BadHire = If the person stayed at the company less than 61 days
Example: If a person joined the company on 27th June 2013 and left on 21st August 2013, then they would be a bad hire.
Which DAX expression would you use for this Calculated Column?
A. Slicer
B. IF(OR((([HireDate]-[TermDate])) >=
61,ISBLANK([TermDate])),0,1)
C. IF(((([HireDate]-[TermDate])*-1) < 61),1,0)
D. IF((([TermDate]-[HireDate])*-1) >= 61,0,1)
A.
For this expression you must use a combination of IF, OR and ISBLANK. If you forget to use ISBLANK then the difference between a date and a blank date is always 0, causing problems with the numeric comparison and potentially always showing BadHire as 1. Remember some employees won’t have left the company and hence have no TermDate value.
151
Many data modeling and data transformations are available when using DirectQuery, though with some limitations. Which of the following is NOT a limitation of using DirectQuery?
A. All tables must come from a single database
B. There is a 1 million row limit for returning data
C. Relationship filtering is limited to a single direction,
rather than both directions
D. 1 GB dataset limitation
D.
152
A large corporate project requires the data team to create a number of different Power BI reports for the company’s internet sales data.
You need to create a measure that estimates the variance of the SalesAmount_USD column from your InternetSales_USD table, for the entire population. The mathematical formula for this calculation should follow the agreed standard:
∑(x - x̃)²/n
Which DAX formula should you use?
A. VAR.P
B. VAR.S
A
153
The internal finance team needs a new dashboard creating to show projected revenue for the coming years so they can adequately plan for future budgets. All of the data for this dashboard will come from SQL Server 2005 that is running on the company’s main SQL Server machine. The IT team has informed you that the SQL Server machine has many instances of SQL Server installed including 2005, 2008, 2012 and even 2016. If the server name was ACMESQL and the instance name provided by IT was SQL2005, what would you type into the Server name textbox when connecting through Power BI?
A. SQL2005\ACMESQL
B. ACMESQL\SQL2005
C. ACMESQL(SQL2005)
D. ACMESQL:SQL2005
B
When connecting to an instance of the Database Engine you must specify the name of the instance of SQL Server. If the instance of SQL Server is the default instance (an unnamed instance), then specify the name of the computer where SQL Server is installed, or the IP address of the computer. If the instance of SQL Server is a named instance (such as SQLEXPRESS), then specify the name of the computer where SQL Server is installed, or the IP address of the computer, and add a slash and the instance name.
154
The DAX FORMAT function is used to convert a value to text according to the specified format that you provide as the second argument to the function. Which DAX expression below would format the number so that it’s displayed as 20.00%?
A. FORMAT( 0.2, “Percent” )
B. FORMAT( 0.2, “p” )
C. FORMAT( 0.2, “%” )
D. FORMAT( 20, “Percent” )
A
155
You want to create a DAX formula that automatically calculates the previous month number. So if the month was June then the formula would return 5. You have already created the DAX formula below:
Today Month = MONTH (Sales[Todays Date])
Which of the DAX formulas below would be the right choice next?
A. Previous Month = Sales[Today Month]-1
B. Previous Month = PREVIOUSMONTH(Sales[Today
Month])
C. Previous Month = STARTOFMONTH(Sales[Today
Month])-1
D. Previous Month = IF (Sales[Today Month] =1,
12,Sales[Today Month]-1)
D
156
nformation Functions (DAX) look at a table or column provided as an argument to another function and tells you whether the value matches the expected type. What is the result of the following DAX expression?
=IF(ISLOGICAL(“true”), "Is Boolean type or Logical", "Is different type")
A. Is Boolean type or Logical
B. Is different type
B
157
The DAX FORMAT function accepts a number of different format strings, some of which can be constructed to create a custom formatter for your exact needs. Study the DAX expression below and choose the right result:
FORMAT(-5, "$#,##0.00;($#,##0.00)")
A. ($5.00)
B. $5.00
C. $5,000.00
D. -$5.00
A
