examtopics Flashcards

Question 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

Question 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

Question 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

Question 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

Question 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

Question 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

Question 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

Answer

A

Visual2, Visual4

Question 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.

Question 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

Answer

A

-> 2. -> 5.

Question 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”

Question 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

Question 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

Question 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.

Question 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.

Question 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.

Question 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

Answer

A

: A
: B
: B

Question 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

Answer

A

-> 5. -> 3. -> 4.

Question 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))

Answer

A

VALUE1: Calculate
VALUE2: Dateadd

Question 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

Question 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.

Question 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).

Question 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.

Question 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])

Question 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.

Question 25

Q

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.

Question 26

Q

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.

Question 27

Q

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]))

Question 28

Q

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

Question 29

Q

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.

Question 30

Q

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.

Question 31

Q

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].

Question 32

Q

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.

Question 33

Q

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

Question 34

Q

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])))

Answer

A

Measure1 =
DIVIDE ([TotalSales], CALCULATE([TotalSales],
ALLSELECTED (Sales[ProductCategory], Sales[ProductSubcategory])))

Question 35

Q

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

Question 36

Q

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"

Answer

A

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"

Question 37

Q

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:

To the Product Subcategory, add a calculated
measure that uses the RELATED(‘Product
Categrory’[Category]) DAX function.
To the Product table, add a column named Category
that uses the RELATED(‘Product Category’[Category])
DAX function.
To the Product table, add a calculated measure that
uses the RELATED(‘Product Category’[Category])
DAX function.
Create a hierachy
To the Product table, add a column named
SubCategory that uses the RELATED(‘Product
Subcategory’[Subcategory]) DAX function.
To the Product Subcategory table, add a column
named Category that uses the RELATED(‘Product
Category’[ProductCategoryKey]) DAX function.

Answer

A

-> 5. -> 4.

Question 38

Q

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)

Question 39

Q

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))

Question 40

Q

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

Question 41

Q

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

Question 42

Q

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.

Question 43

Q

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-“)

Answer

A

Text.Replace([SSN], Text.Start([SSN],4), “xxx-“)

Question 44

Q

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”)

Question 45

Q

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

Answer

A

ErrorCode: D
SunStatus: C

Question 46

Q

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.

Question 47

Q

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

Question 48

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 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

Question 49

Q

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

Question 50

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 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

Question 51

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 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

Question 52

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 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

Question 53

Q

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

Question 54

Q

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

Question 55

Q

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

Question 56

Q

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

Question 57

Q

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.

Answer

A

B, (D also possible)

Question 58

Q

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

Question 59

Q

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.

Question 60

Q

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.

Question 61

Q

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.

Question 62

Q

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

Question 63

Q

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.

Question 64

Q

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.

Answer 12

A

-> 4. -> 1

Answer 13

A

B

Correct is:
#”Replaced Errors” = Table.ReplaceErrorValues(#”Changed Type”, {{“CustomerID”, 0}})
in
#”Replaced Errors”

Answer 14

A

Schedule Display = IF(COUNTROWS(Schedule>0,

UNICHAR(9635), “”)

Answer 15

A

LargerSales = COUNTROWS(CALCULATE(‘FactInternetSales’, ‘FactInternetSales’[SalesAmount > 1000))

Answer 16

A

Reports that support Publish to web: Report1 only

Reports that can be published to Power BI Report Server: All three Reports

Answer 17

A

CALCULATE(
DATEADD(
LASTNONBLANK(Sales[OrderDate];
SUM(Sales[SalesAmount])); -12;MONTH))

return
CALCULATE(SUMX(FILTER(Sales;Sales[OrderDate]>varlast12m);
 Sales[SalesAmount]))

Answer 18

A

Goal = CALCULATE(COUNT(‘Subscriber’[SubscriberID]), PREVIOUSYEAR(‘Date’[Date]))*1.05

Answer 19

A

-> 5. -> 1.

Answer 20

A

Top Customers = CALCULATE(SUM(Sales[Profit]),

TOPN(10,Customer,SUM(Sales[Profit])))

Answer 21

A

A: One-to-one
B: One-to-one
C: One-to-many

Answer 22

A

-> 3. -> 5.

Answer 23

A

YTDPreviousSales = CALCULATE (SUM(FactInternetSAles[SalesAmount]), DATEADD(DimDate[Date], -12, MONTH))

Answer 24

A

Column = CONCATENATE (FORMAT(MONTH([Date_name]), “MMM”), (FORMAT(YEAR([Date_name]), “yyyy”))

Answer 25

A

E. No Action needed, but datatypes matter when craeting a relationship. So make shure Store_ID in both tables is in the same datatype

Answer 26

A

A (but not best way)

Answer 27

A

✑ Sales to Product
A, B
✑ Sales to Salesperson
A, A

Answer 28

A

Value: C
Maximum value: B
Target value: B

Answer 29

A

Text.AfterDelimiter([phone_number], “x”, {0, RelativePosition.FromEnd})

Answer 30

A

= Table.AddColumn(#”Previous Step”, “Custom”, each Text. Replace(Text.End([Phone],12), “ “, “-“))

Answer 31

A

B Use “Replace Errors”

Answer 32

A

B, last click is Export not Upload

Answer 33

A

B Need to click “Import” not “Connect”

Answer 34

A

C (but all four data Sources are supported)

Answer 35

A

A (fehler beim Übertragen der Antworten?)

Answer 36

A

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.

Answer 37

A

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.

Brainscape's Knowledge GenomeTM

examtopics Flashcards

https://www.examtopics.com/exams/microsoft/70-778/view/

Brainscape's Knowledge Genome^TM