Question 3:

There is a table visual that displays Total Sales each quarter and the
corresponding sales for the previous quarter from the Sales table.

The two measures used in the table are defined as below:

1. Total Sales =
2. CALCULATE ( SUM ( Sales[Sales Amount] ) )
1. Sales Last Quarter =
2. 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.

Question 4:
This question is part of repeated scenario questions that contain the same
stem but with a different solution for each question. You need to identify if
the given solution solves a particular problem. Each set of repeated
scenario questions might contain either none, one, or many solutions.
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:

1. Last 7-day EPC =

2. DIVIDE (
3. CALCULATE (
4. [Total Affiliate Revenue],
5. DATESINPERIOD ( 'Date'[Date], 'Date'[Date] - 1, -7, DAY )
6. ),
7. CALCULATE (
8. [Total Clicks],
9. DATESINPERIOD ( 'Date'[Date], 'Date'[Date] - 1, -7, DAY )
10. )
11. ) * 100
Does the solution meet the goal? Yes/ no

Note: The measure Total Affiliate Revenue is defined as SUM ( 'Affiliate

Revenue'[Affiliate Income] )
The measure Total Clicks is defined as SUM ( 'Affiliate Revenue'[Clicks] )

Solution: He creates the below measure:

1. Last 7-day EPC =

2. DIVIDE (
3. CALCULATE (
4. [Total Affiliate Revenue],
5. DATESINPERIOD ( 'Date'[Date], MIN ( 'Date'[Date] ) - 1, -7,
DAY )
6. ),
7. CALCULATE (
8. [Total Clicks],
9. DATESINPERIOD ( 'Date'[Date], MIN ( 'Date'[Date] ) - 1, -7,
DAY )
10. )
11. ) * 100

Does the solution meet the goal?

Note: The measure Total Affiliate Revenue is defined as SUM ( 'Affiliate

Revenue'[Affiliate Income] )
The measure Total Clicks is defined as SUM ( 'Affiliate Revenue'[Clicks] )
Solution: He creates the below measure:

1. Last 7-day EPC =

2. DIVIDE (
3. CALCULATE (
4. [Total Affiliate Revenue],
5. DATEADD ( 'Date'[Date], -7, DAY )
6. ),
7. CALCULATE (
8. [Total Clicks],
9. DATEADD ( 'Date'[Date], -7, DAY )
10. )
11. ) * 100

Does the solution meet the goal?

Note: The measure Total Affiliate Revenue is defined as SUM ( 'Affiliate

Revenue'[Affiliate Income] )
The measure Total Clicks is defined as SUM ( 'Affiliate Revenue'[Clicks] )
Solution: He creates the below measure:
 1. Last 7-day EPC =
2. DIVIDE (
3. CALCULATE (
4. [Total Affiliate Revenue],
5. DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ) - 7, MIN
( 'Date'[Date] ) -1 )
6. ),
7. CALCULATE (
8. [Total Clicks],
9. DATESBETWEEN ( 'Date'[Date], MIN ( 'Date'[Date] ) - 7, MIN
( 'Date'[Date] ) -1 )
10. )
11. ) * 100

Does the solution meet the goal?

Note: The measure Total Affiliate Revenue is defined as SUM ( 'Affiliate

Revenue'[Affiliate Income] )
The measure Total Clicks is defined as SUM ( 'Affiliate Revenue'[Clicks] )

Question 9:
Koovs is an online e-commerce store based in India that sells women’s
shoes. On each of their product pages, they display two
metrics: Product ratings and Answered questions.

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?
Question 11:
You have a transactional Sales table with data of orders fulfilled by a
company for several vendors on a single day (with the below schema).

Note: Vendor information is confidential.

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?

Question 12:
An automobile manufacturing company sources parts from suppliers in
different regions of the country. The Supplier and the Purchase table
have a one-to-many relationship through the Supplier Key column (shown
below).
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?

Question 13:
You have a dataset on the bulk orders your company received from across
India via different Sales Channels.

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?

Question 14:
You build a Power BI report for graduates of a b-school. Given below is a
sample dataset.

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?

Question 15:
Business users in your organization connect to a financial data mart to
develop financial reports like income analysis, P&L analysis, etc., and
deploy them to service. Over time, this leads to a proliferation of datasets
and reports and inefficient workspace storage consumption as many
reports have a good overlap of measures & KPIs.
Which of the following would you choose to optimize the self-service BI
implementation?

Question 16:
You have a dataset of the height and weight of Grade 10 students.

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?
Question 18:
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 and the data
model.

He would like to calculate the average income he earns per transaction

day from all the affiliate channels across different years.
Which two steps can help him achieve the goal?
Note: On a transaction day, at least one transaction occurred.

Question 20:
You have the below data model imported into Power BI.
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?
Question 24:
You import tables from the Adventure Works Data warehouse and design
the below report page.

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?
Question 26:
You create a quick measure Sales Amount MTD to display the Month-to-
date total of Sales Amountvalues using Order Date (see the below
representation).

In the visual, the Order Date in the Values well uses the Power BI auto
date/time generated hierarchy (Year, Quarter, Month, and Day). You have
to replace the Year, Quarter, Month & Day columns with a single Date
column without breaking the visual.
Which of the following solutions would you use?

Question 27:
This question is part of repeated scenario questions that contain the same
stem but with a different solution for each question. You need to identify if
the given solution solves a particular problem. Each set of repeated
scenario questions might contain either none, one, or many solutions.
You have a list of customers in an Excel spreadsheet and a Sales table in
on-premises SQL Server. You need to combine the two into a single table
of only the customers who have not purchased any product. Finally, load
this data into Power BI. Ensure that you minimize the size of the data
model.
Based on the given scenario, choose the correct set of options:
How do you combine the Customer & the Sales table?

What join kind would you perform? (Assume Sales is the right table)

What would you do to the original two queries?

You choose Append Queries as New, Right Anti, & Delete them
Does the solution solve the problem? Yes/ No
Question 27:
This question is part of repeated scenario questions that contain the same
stem but with a different solution for each question. You need to identify if
the given solution solves a particular problem. Each set of repeated
scenario questions might contain either none, one, or many solutions.
You have a list of customers in an Excel spreadsheet and a Sales table in
on-premises SQL Server. You need to combine the two into a single table
of only the customers who have not purchased any product. Finally, load
this data into Power BI. Ensure that you minimize the size of the data
model.
Based on the given scenario, choose the correct set of options:

How do you combine the Customer & the Sales table?

What join kind would you perform? (Assume Sales is the right table)

What would you do to the original two queries?

You choose Append Queries as New, Right Anti, & Delete them
Does the solution solve the problem? Yes/ no
Question 29:
This question is part of repeated scenario questions that contain the same
stem but with a different solution for each question. You need to identify if
the given solution solves a particular problem. Each set of repeated
scenario questions might contain either none, one, or many solutions.
You have a list of customers in an Excel spreadsheet and a Sales table in
on-premises SQL Server. You need to combine the two into a single table
of only the customers who have not purchased any product. Finally, load
this data into Power BI. Ensure that you minimize the size of the data
model.
Based on the given scenario, choose the correct set of options:

How do you combine the Customer & the Sales table?

What join kind would you perform? (Assume Sales is the right table)

What would you do to the original two queries?

You choose Merge Queries as New, Left Anti, & Disable Enable Load
Does the solution solve the problem? Yes/ no
Question 30:
This question is part of repeated scenario questions that contain the same
stem but with a different solution for each question. You need to identify if
the given solution solves a particular problem. Each set of repeated
scenario questions might contain either none, one, or many solutions.
You have a list of customers in an Excel spreadsheet and a Sales table in
on-premises SQL Server. You need to combine the two into a single table
of only the customers who have not purchased any product. Finally, load
this data into Power BI. Ensure that you minimize the size of the data
model.
Based on the given scenario, choose the correct set of options:

How do you combine the Customer & the Sales table?

What join kind would you perform? (Assume Sales is the right table)

What would you do to the original two queries?

You choose Merge Queries as New, Left Anti, & Disable Include in report
refresh
Does the solution solve the problem?

Question 31:
While importing data into Power BI, you have to remove columns with
column names that start with the prefix Test_ as shown below (Not all
columns displayed).

Given below is an incomplete M query. Choose the correct options that

complete the M query to accomplish the required task.
Question 32:
Below is an imported table in Power Query.

There is a project requirement to extract and modify the above column

names (in camel case) as shown in the table below.

Select and reorder the steps you would perform that meet the
requirement.
Question 33:
In the Promotion dimension table, there are two columns Promotion
Category and Maximum Quantity. The column Maximum Quantity has four
distinct whole number values and 12 null values.

For the following scenarios, choose the correct number of rows displayed
in the visual (including null values).
Question 34:
You have an employee table that stores hierarchical records of employees
and their managers.

Which of the following DAX functions should you use if you need to create
a calculated column that displays the second-line manager (where
applicable) for each employee?
Note: An employee’s second-line manager is his manager’s manager.
Question 35:
From the sales table, you create the below bubble chart in Power BI.

Each bubble:
a. is plotted along the axes that represent the average quantity
ordered and average sales.
b. plots these average values for a country and product line, with bubble
size, indicative of its sales.
You need to display how the relationship between average quantity
ordered and average saleschanges over several years. What would you
do?