Microsoft Power BI Test - 15

You have a data model with the Date table related to the Offline Sales table by columns of date data type (Date[Date] -> ‘Offline Sales‘[Due Date]). The Date table is marked as a date table.

Goal: You have to use the appropriate date field to create a quick measure for calculating the Month-over-month change of the Sales Amount.
Solution: You use the Order Date column from the Offline Sales table in the Date field.

Does the solution meet the stated goal?

Select 1 option(s):

You have to create a Power BI visual to monitor Microsoft’s stock price along with the relevant information below:
· 52-week high,
· 52-week low
· Current Microsoft stock price
· Stock’s 50 Day Moving Average (DMA)
You track the stock price, and if the price moves above/below 50 DMA, you plan to buy/sell the stock in large quantities. Which visual would you use?

Select 1 option(s):

What could be the reason for his observation?

Select 1 option(s):

The sales managers at Apple have to analyze the Sales Amount for each supplier. To achieve this objective, determine if you need to make any changes to the above data model.

Select 1 option(s):

Select 1 option(s):

Use query parameters instead of hardcoding data source connection details.
You create a Power BI model that combines data from 3 data sources: Azure SQL Server, Analysis Services, and Excel spreadsheet.
You realize that you can browse the Excel file and import data into your model, but Excel doesn‘t support data source parameterization in the default data connector.
How do you parameterize the Excel data source connection? (Select two possible options).

Select 2 option(s):

Some users complain that the visual Var Plan by Country/Region is slow to load. You use the performance analyzer to examine the performance of the visual. You observe that the Other tasks require an inordinate amount of time compared to DAX query or Visual display tasks.

From the performance analyzer learnings, how would you fix this issue?

Select 1 option(s):

So, he creates a KPI visual to compare how the revenue in the current quarter compares to the revenue from the previous quarter.
The below representation shows the measures/fields used to build the KPI visual.

But there is something off with the visual. The KPI displays a much higher value (33859) than the goal (Revenue Last Quarter). But you cannot observe this behavior in the trend chart in the background (which is continuously decreasing each quarter).
To debug the issue, you check the underlying data for the visual.

What could be wrong with the visual?
Note:
1. Offline Store Revenue table contains historical (at least four years) revenue data for the company’s offline stores.
2
Revenue Last Quarter =
IF (ISBLANK([Total Revenue]), BLANK(), CALCULATE ( [Total Revenue], PREVIOUSQUARTER( ‘Date‘[Date] ) ) )

Select 1 option(s):

You have to find the percentage of transactions with empty Product ID values. Which data preview tool in Power Query is the most suitable choice?

Select 1 option(s):

Which of the following could be a possible reason?

Select 1 option(s):

Which of the following solutions meet the stated goals? Select two options.

Select 2 option(s):

You create the below Power BI report.

You need to design the Sales Value measure used in the table visual. This measure:
1. Should display empty values only if the report is filtered by the Date field from the Dates table.
2. Should honor any other filter and display [Total Sales] values if the report is filtered/sliced by other fields in the model.
How would you complete box 1, box 2, and box 3 respectively for the Sales Value measure?

Select 1 option(s):

1. BFSI
2. Life sciences
3. Manufacturing & hi-tech
4. Energy & Utilities
5. Communications & services.
The company develops a Power BI report that displays project information in these verticals. They define row-level security roles to enable bulk access for the SPMs, DMs, BU heads, etc., to the project‘s insights in their vertical. i.e., they can access project data only for their verticals via Azure AD security groups.
You, a delivery manager (DM) in the BFSI space, received a transfer to the Life sciences vertical.
How should your access be managed in Power BI?

Select 1 option(s):

The company can receive orders from multiple clients in a city. You need to display the below information in a Power BI report:
Requirement 1: The count of cities where at least an order is placed for each State Province in January 2015.
Requirement 2: The all-time percentage contribution of a State Province to the total quantities ordered in the US.
How would you configure the relationship from the Order table to the City table?

Select 1 option(s):

Below is the data lineage diagram for the dashboard.

Based on the above representation, Choose Yes if the statement is correct. Else, choose No.

Select 1 option(s):

In this visual, you have to add a measure to display the sales for the North district only, while leaving the sales of other districts a blank value. Which DAX formula would you use? Select two options.
Note:
Total Sales =
SUM ( ‘Work Orders‘[Sales] )

Select 1 option(s):

Below is a Power BI table showing the aggregation (SUM) of pageviews for each study guide.

In the above table, you have to format/color the study guides whose average pageviews are greater than 80. How would you configure the conditional formatting settings?

Select 1 option(s):

Note: A sprint runs for one week; the project is six months in duration.

Select 1 option(s):

Below is the sample data of a popular affiliate marketer from India. The dataset contains information on the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.

You have created two report pages in Power BI Desktop.
The report Clicks by Advertiser has a column chart displaying the number of clicks for each Advertiser. There is also an Affiliate channel slicer in this report.

The report Sales by Advertiser has a bar chart displaying the Sales Amount for each Advertiser. There is also a Date slicer in this report.

You have to ensure that when a business user filters the first report page by any Affiliate channel, the same filters apply to the second report page as well. Which of the following options would you choose?

Select 1 option(s):

Below is the sample data of a popular affiliate marketer from India. The dataset contains information on the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.

You have created two report pages in Power BI Desktop.
The report Clicks by Advertiser has a column chart displaying the number of clicks for each Advertiser. There is also an Affiliate channel slicer in this report.

The report Sales by Advertiser has a bar chart displaying the Sales Amount for each Advertiser. There is also a Date slicer in this report.

Some records in the dataset have unknown values in the Affiliate Channel column [Observe the (blank) value in the Affiliate Channel slicer above].
So, you apply a visual-level filter to filter out the blank value in the slicer.

You have to ensure that the Date slicer (on the second report page) displays only the dates that do not have any blank values for Affiliate Channel.
What two actions would you do?
Note: You have already synced both the slicers.

Select 2 option(s):

Below is the sample data of a popular affiliate marketer from India. The dataset contains information on the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.

You have created two report pages in Power BI Desktop.
The report Clicks by Advertiser has a column chart displaying the number of clicks for each Advertiser. There is also an Affiliate channel slicer in this report.

The report Sales by Advertiser has a bar chart displaying the Sales Amount for each Advertiser. There is also a Date slicer in this report.

You receive a new request from a stakeholder to display Year values on slicers in both the report pages. You have already replaced the Date values on the 2nd slicer with Year values.

What two actions would you perform to ensure that the other slicer automatically updates with Year values?
Note: You have already synced both the slicers.

Select 2 option(s):

Below is the sample data of a popular affiliate marketer from India. The dataset contains information on the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.

You have created two report pages in Power BI Desktop.
The report Clicks by Advertiser has:
· A column chart displaying the number of clicks for each Advertiser.
· An Affiliate Channel slicer.
· In addition, you added a Line chart that displays Clicks by Year

The report Sales by Advertiser has a bar chart displaying the Sales Amount for each Advertiser. There is also a Date slicer in this report.

What two actions would you perform to ensure that if a business user selects any date in the Date slicer, all visuals on the first report page, except the Line chart, are filtered?
Note: You have NOT synced both the slicers yet.

Select 2 option(s):

Below is the sample data of a popular affiliate marketer from India. The dataset contains information on the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.

You have created two report pages in Power BI Desktop.
The report Clicks by Advertiser has:
· A column chart displaying the number of clicks for each Advertiser.
· An Affiliate Channel slicer.
· In addition, you added a Line chart that displays Clicks by Year

The report Sales by Advertiser has a bar chart displaying the Sales Amount for each Advertiser. There is also a Date slicer in this report.

What two actions would you perform to ensure that if a business user selects any date in the Date slicer, all visuals on the first report page, except the Line chart, are filtered?
Note: You have synced both the slicers.

Select 2 option(s):

Which of the following actions do you need to perform?

Select 1 option(s):

Based on the dataset and the problem, answer the following statements. If the given statement is correct, choose Yes. Else choose No.

Note 1. An order can have multiple transactions.
2. Some column names like Sales Order Number and Sales Order Line Number have the same semantics as that in the familiar AdventureWorks data warehouse.

Select 1 option(s):

He creates a 2-column calculated table [Product subcategory, Average Sales] that returns only the product subcategories whose average sales are higher than the average sales of all the products. He has to complete the below DAX expression:

Fill in the given boxes with the most appropriate choices.

Select 1 option(s):

Which of the following operations would you use to parse the JSON data to Power BI columns?

Select 1 option(s):

Select 1 option(s):

The two measures used in the table are defined as below:
Total Sales =
CALCULATE ( SUM ( Sales[Sales Amount] ) )
Sales Last Quarter =
CALCULATE ( [Total Sales], PREVIOUSQUARTER( ‘Date‘[Date] ) )
Due to the workings of the PREVIOUSQUARTER DAX function, a blank value appears for Total Sales in the last row when you add the Sales Last Quarter measure to the visual.
How would you rewrite the measure for Sales Last Quarter in order to avoid this blank value? Select two options.

Select 2 option(s):

Harsh Agarwal is a famous affiliate marketer from India. Below is a sample data that displays the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.
Carefully observe the sample Affiliate Revenue dataset below.

In the affiliate marketing industry, EPC describes the money earned per 100 clicks sent to a merchant’s site. He needs to calculate the 7-day EPC (EPC for the last seven days).
Solution: He creates the below measure:
Last 7-day EPC =
DIVIDE (
CALCULATE (
‘Affiliate Revenue‘[Total Affiliate Revenue],
DATESINPERIOD ( ‘Date‘[Date], ‘Date‘[Date] – 1, -7, DAY )
),
CALCULATE (
‘Affiliate Revenue‘[Total Clicks],
DATESINPERIOD ( ‘Date‘[Date], ‘Date‘[Date] – 1, -7, DAY )
)
) * 100
Does the solution meet the goal?

Select 1 option(s):

Harsh Agarwal is a famous affiliate marketer from India. Below is a sample data that displays the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.
Carefully observe the sample Affiliate Revenue dataset below.

In the affiliate marketing industry, EPC describes the money earned per 100 clicks sent to a merchant’s site. He needs to calculate the 7-day EPC (EPC for the last seven days).
Solution: He creates the below measure:
Last 7-day EPC =
DIVIDE (
CALCULATE (
‘Affiliate Revenue‘[Total Affiliate Revenue],
DATESINPERIOD ( ‘Date‘[Date], MIN ( ‘Date‘[Date] ) – 1, -7, DAY )
),
CALCULATE (
‘Affiliate Revenue‘[Total Clicks],
DATESINPERIOD ( ‘Date‘[Date], MIN ( ‘Date‘[Date] ) – 1, -7, DAY )
)
) * 100
Does the solution meet the goal?

Select 1 option(s):

Harsh Agarwal is a famous affiliate marketer from India. Below is a sample data that displays the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.
Carefully observe the sample Affiliate Revenue dataset below.

In the affiliate marketing industry, EPC describes the money earned per 100 clicks sent to a merchant’s site. He needs to calculate the 7-day EPC (EPC for the last seven days).
Solution: He creates the below measure:
Last 7-day EPC =
DIVIDE (
CALCULATE (
‘Affiliate Revenue‘[Total Affiliate Revenue],
DATEADD ( ‘Date‘[Date], -7, DAY )
),
CALCULATE (
‘Affiliate Revenue‘[Total Clicks],
DATEADD ( ‘Date‘[Date], -7, DAY )
)
) * 100
Does the solution meet the goal?

Select 1 option(s):

Harsh Agarwal is a famous affiliate marketer from India. Below is a sample data that displays the traffic (Clicks) and Affiliate Income he generates from different advertisers via his blog.
Carefully observe the sample Affiliate Revenue dataset below.

In the affiliate marketing industry, EPC describes the money earned per 100 clicks sent to a merchant’s site. He needs to calculate the 7-day EPC (EPC for the last seven days).
Solution: He creates the below measure:
Last 7-day EPC =
DIVIDE (
CALCULATE (
‘Affiliate Revenue‘[Total Affiliate Revenue],
DATESBETWEEN ( ‘Date‘[Date], MIN ( ‘Date‘[Date] ) – 7, MIN ( ‘Date‘[Date] ) -1 )
),
CALCULATE (
‘Affiliate Revenue‘[Total Clicks],
DATESBETWEEN ( ‘Date‘[Date], MIN ( ‘Date‘[Date] ) – 7, MIN ( ‘Date‘[Date] ) -1 )
)
) * 100
Does the solution meet the goal?

Select 1 option(s):

Select 1 option(s):

Here is a view of the sample dataset imported into Power BI.

You have a column chart that displays the total sales for each Product Category.

When a sales manager hovers over an individual Product Category (column), you should show a tooltip that displays the metrics no. of. ratings and no. of. answered questions for each product in the product category.
How would you achieve this objective?

Select 1 option(s):

Select 1 option(s):

Carefully observe the sample Affiliate Revenue dataset below.

He would like to calculate the average income he earns per day from all the affiliate channels across different years.
Which two steps can help him achieve the goal?

Select 1 option(s):

In the Power BI model, you have to load the best-selling product and their Units Sold value for each product category.
As part of the first step, you have already created a table structure for each product category with the Group by operation All rows.

For the next step, you should write a Power Query formula to extract the best-selling product from the table structure for each product subcategory.
Which of the below formula you would write in a custom column?

Select 1 option(s):

You have to create a new calculated table with records only for the top 2 regions where the company orders the most no. of units. Which of the following DAX formula would you use?

Select 1 option(s):

You have to create a transformation query for the No of days the bulk orders are received from each City and Sales channel. Which of the following options would you choose?

Select 1 option(s):

The final report has the following requirements:
1. When a student logs in, the report should display their name (Graduate Name) and Grades.
2. The report should display their peer grades without revealing personal information (name) about whom those grades belong. That is, the report should not display the names of their peers.
3. The users should be able to compare their performance with the performance of the batch mates.
How would you create the report?

Select 1 option(s):

Which of the following would you choose to optimize the self-service BI implementation?

Select 1 option(s):

You need to use both height and weight to visualize and identify students who are very tall, a cut above the rest of the batch, for an inter-school basketball match.
Which Power BI visual would you use?

Select 1 option(s):

How would you manage employees’ access to reports?

Select 1 option(s):

Note: Assume the fact table has a large number of columns.

Select 1 option(s):

To display the Average Product Category Rating, your colleague creates a calculated column in the Product Category table with the below DAX formula.

Problem Statement: To support real-time analytics, you plan to change the data storage mode from import to DirectQuery. However, in DirectQuery, the RELATEDTABLE function does not work in a calculated column.
Which of the following changes can you NOT do to ensure users can still analyze Average Product Category Rating in DirectQuery mode?

Select 1 option(s):

Select 1 option(s):

Note: Ignore all aspects of Power BI licensing.

Select 1 option(s):

Which of the following visuals would you use?

Select 1 option(s):

When the user selects any of the Organization Name (on the column chart) and a Year (from the slicer) and clicks the Show Data button, you should show a detailed report page for the selected Organization Name and Year.
How would you configure the report?

Select 1 option(s):