Microsoft Power BI Data Analyst (PL-300) Practice Test 16

The Permissions table defines the Account Types that individual employees can access.

Select and order the actions you need to perform to ensure that dynamic row-level security works for users logged into Power BI.

Note: A security role is created on the Permissions table with the DAX expression: [User Name] = USERPRINCIPALNAME ()

Select 1 option(s):

How would you set up the data connection from Power BI?

Select 1 option(s):

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

The Sales Manager has to understand the relationship between No. of. ratings and No. of. answered questions. They also need to find high-performing product categories (based on these metrics).
Which chart best meets the needs of the sales manager?

Select 1 option(s):

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

Select 1 option(s):

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 Date column from the Date table in the Date field.

Does the solution meet the stated goal?

Select 1 option(s):

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 Due Date column from the Offline Sales table in the Date field.

Does the solution meet the stated goal?

Select 1 option(s):

You create a Power BI model (shown below) to analyze the yearly income of your customers across different occupations and sales territories.

You have to grant report access so users can view customer’s income data only for the high-profile occupation Management in the Sales Territory Region Germany.
Solution: You create a row-level security role. This role contains two filters:
DimSalesTerritory[Sales Territory Region] = “Germany“ and DimCustomer[Occupation] = “Management“
You assign this role to the user.
Does the solution meet the goal?

Select 1 option(s):

To the existing Power BI model, you import one more table DimEmployee to analyze the yearly income of your customers across different occupations and sales territories.

You have to grant report access so each employee can access the customer’s income and occupation data only for the Sales Territory Region he is assigned.
Solution: You create a row-level security role with the filter:
DimEmployee [Email Address] = USERNAME ()
You assign this role to all your employees.
Does the solution meet the goal?

Select 1 option(s):

You have to create a table chart that displays the total sales value of the last three transactions for each product.

How would you create the measure for the visual?

Select 1 option(s):

When users run a query Affiliate Income by date hierarchy, they get correct results. But, when they run Affiliate Income by Calendar, they do not get any results.
You have to set up the model so that the users can query the model with Affiliate Income by Calendar and get the same results.
What would you do?

Select 1 option(s):

You want to view the sales value every week. Which of the following is the easiest/elegant option?

Select 1 option(s):

By looking at the above chart, you realize that data is missing in Iceland, Northern Russia, Scandinavian & several other countries (marked ?).
What do you think is the reason behind the missing data?

Select 1 option(s):

What is the best way to split this chart into multiple visuals so you can analyze the data side-by-side across different Years?

Select 1 option(s):

Which tool cannot indicate whether all the performed transformations are folded (i.e., if query folding occurs) to SQL Server?

Select 1 option(s):

Which of the following is the best way to test your reports across all the environments?

Select 1 option(s):

You need to ensure that each Ordered Product belongs to a separate row. Which operation would you use?

Select 1 option(s):

What does the dotted Average line indicate?

Select 1 option(s):

· Import a sample data into Power BI Desktop from the Sales fact table that contains more than 350,000 transactions.
· Choose the most efficient method to fetch the data.
Which of the following solutions would you implement? Choose two options.

Select 2 option(s):

Below is a data model with details of daily Microsoft’s stock prices for the year 2021.

Which of the following DAX formulas would you use to display the Close value of the Microsoft stock each month?
Note:
MS Close Price =
SUM ( ‘MS Stock Prices‘[Close] )

Select 1 option(s):

After some months, you have a request from the sales team to build a sales dashboard.
While developing sales reports, you observe that you apply a similar set of multiple-step transformations to create the Product dimension table in Power Query. You realize this is a duplication of efforts and want to streamline the data transformation process for future requests.
Which of the following would you do?

Select 1 option(s):

Your manager asked you to create a quick measure for the weekly rolling average to smooth out daily fluctuations.

How would you complete the quick measure?

Select 1 option(s):

What does the value 19462 indicate?

Select 1 option(s):

You have to visualize the running headcount of the organization (as employees leave/join) each month. Which chart would you use?
Note: Status 1 indicates that an employee is hired. Status -1 indicates that the employee is terminated/resigned.

Select 1 option(s):

(Select 2 options)

Select 2 option(s):

To visually communicate this aspect, the client asks you to replace Sales Last Year QoQ% values with color bands that convey the magnitude and direction of change in QoQ percentage values.
What can you do to achieve this?

Select 1 option(s):

As a trader, if Apple’s stock price goes above 200 DMA, you enter a position. If Apple’s stock goes below 200 DMA, you exit a position.

Accordingly, you want Power BI to notify you of such price movements. What would you do?

Select 1 option(s):

You have a simple data model with the Product & the Sales table. You have to compute the highest total sales value for any product each month.

Solution: You write the below DAX formula:
Highest sales value for any Product =
MAXX (
‘Product‘,
SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] )
)
Does the solution meet the stated goal?

Select 1 option(s):

You have a simple data model with the Product & the Sales table. You have to compute the highest total sales value for any product each month.

Solution: You write the below DAX formula:
Highest sales value for any Product =
MAXX (
‘Product‘,
SUMX ( RELATEDTABLE ( Sales ), Sales[Quantity] * Sales[Unit Price] )
)
Does the solution meet the stated goal?

Select 1 option(s):

You have a simple data model with the Product & the Sales table. You have to compute the highest total sales value for any product each month.

Solution: You write the below DAX formula:
Highest sales value for any Product =
MAXX (
‘Product‘,
CALCULATE ( SUMX ( Sales, Sales[Quantity] * Sales[Unit Price] ) )
)
Does the solution meet the stated goal?

Select 1 option(s):

You have a simple data model with the Product & the Sales table. You have to compute the highest total sales value for any product each month.

Solution: You write the below DAX formula:
Highest sales value for any Product =
MAXX ( ‘Product‘, CALCULATE ( [Total Sales] ) )
Does the solution meet the stated goal?

Select 1 option(s):

You have a simple data model with the Product & the Sales table. You have to compute the highest total sales value for any product each month.

Solution: You write the below DAX formula:
Highest sales value for any Product =
MAXX ( ‘Product‘, [Total Sales] )
Does the solution meet the stated goal?

Select 1 option(s):

If an HR manager selects any gender from the pie chart, the following interactions should happen on other visuals in the report:
1. The column chart should display the new hire data of all the regions for the selected gender.
2. The line chart shall show the nature of employment data every month for the selected gender.
3. The clustered column chart should display a section of new hires every month for the selected gender.
How would you configure the interactions between the visuals?

Select 1 option(s):

You need to calculate and display the EBITDA margin on a card visual in Power BI Desktop.
Note: EBITDA margin is the company‘s operating profit (EBITDA) as a percentage of its Revenue.
Which of the following would you create?

Select 1 option(s):

How would you optimize the column storage while still retaining all the relevant information?

Select 1 option(s):

Which of the following features would you use?

Select 1 option(s):

You have to create a table chart that displays the sum of the highest three sales transactions for each product.

How would you create the measure for the visual?

Select 1 option(s):

Below is a Power BI Column chart that displays the Total Marks obtained by 15 Grade X students in Marion Cross School.

You need to create a reference line that shows which students are above/below the median class marks.
Solution: You create a constant line and set the Value to 300
Does the solution meet the goal?

Select 1 option(s):

Below is a Power BI Column chart that displays the Total Marks obtained by 15 Grade X students in Marion Cross School.

You need to create a reference line that shows which students are above/below the median class marks.
Solution: You create an average line using the Total Marks measure.
Does the solution meet the goal?

Select 1 option(s):

Below is a Power BI Column chart that displays the Total Marks obtained by 15 Grade X students in Marion Cross School.

You need to create a reference line that shows which students are above/below the median class marks.
Solution: You create a percentile line using the Total Marks measure and set the Percentile to 50%
Does the solution meet the goal?

Select 1 option(s):

Below is a Power BI Column chart that displays the Total Marks obtained by 15 Grade X students in Marion Cross School.

You need to create a reference line that shows which students are above/below the median class marks.
Solution: You create a median line using the Total Marks measure.
Does the solution meet the goal?

Select 1 option(s):

You have to analyze the maintenance events by the event date. What would you do?
Note: In the future, you may need to analyze the event data every hour.

Select 1 option(s):

You have to analyze Reseller Sales for each Sales Territory. How would you configure the relationship between Dim Geo and Dim Territory?

Select 1 option(s):

Select the correct M query expression you would use to replace null values with a 0 in Power Query.

Select 1 option(s):

Which chart is best suited for this scenario?

Select 1 option(s):

Note:
[Total Order Quantity] =
SUM ( ‘Purchase‘[Ordered Quantity] )

Select 2 option(s):

Select 1 option(s):

Which of the following is the best way to surface the video?

Select 1 option(s):

You have to find the percentage of transactions performed by non-unique customers. Which data preview tool in Power Query is the most suitable choice?

Select 1 option(s):

When you import this JSON file into Power BI, the Source step is a List of Records.

Rearrange the remaining order of steps to flatten out the JSON file into a Power BI table.

Select 1 option(s):

The users accessing data from a mobile device should view all the dashboard tiles except those on sales/profits. Select and order three steps you would perform to create a mobile dashboard layout that displays the information in the best possible way.

Select 1 option(s):

where Total Sales and Sales Last Year are measures defined by the following DAX formulas:
Total Sales =
CALCULATE ( SUM ( Sales[Sales Amount] ) )
Sales Last Year =
CALCULATE ( [Total Sales], PREVIOUSYEAR ( ‘Date‘[Date] ) )
Why do you think the goal value is blank?

Select 1 option(s):

When a user places the cursor on any individual column in the column chart, the visual should display additional data points like Average Units Sold, Highest discount offered.
How would you achieve this objective?

Select 1 option(s):

You need to apply Power Query transformation steps and shape the data in the below format:

Select the steps you would apply in the correct order.

Select 1 option(s):

Select 2 option(s):

Task: You have to hide the customer’s card PIN from report viewers.
Which of the following tasks would you perform that optimizes the model size?

Select 1 option(s):

You have to create a table visual that displays both the Delivery Amount and the Order Amount each year in the same table. Can you build the visual?
Assume the auto date/time option is set to ON.

Select 1 option(s):

Which of the following measures would you create? Select two options.

Select 2 option(s):

In the Power BI model, you have to load only the product and the number of Units Sold of the biggest order for each product category.
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Select 1 option(s):

You have to calculate the Quarter-to-date sales values from the Sales table. Below is the given data model.

Solution: You perform the following steps:
a. Create a relationship between the date column in the Date table and the Order Date column in the Sales table
b. Mark the Date table as a date table.
c. You create the measure Sales QTD = CALCULATE([Sales Amount], DATESQTD(‘Date‘[Date]))
Do the above steps help you to calculate the QTD sales values?

Select 1 option(s):

You have to calculate the Quarter-to-date sales values from the Sales table. Below is the given data model.

Solution: You perform the following steps:
a. Create a relationship between the date column in the Date table and the Order Date column in the Sales table
b. You do not Mark the Date table as a date table.
c. You create the measure Sales QTD = CALCULATE([Sales Amount], DATESQTD(‘Date‘[Date]))
Do the above steps help you to calculate the QTD sales values?

Select 1 option(s):

You have to calculate the Quarter-to-date sales values from the Sales table. Below is the given data model.

Solution: You perform the following steps:
a. Create a relationship between the Date Key column in the Date table and the Order Date Key column in the Sales table
b. Mark the Date table as a date table.
c. You create the measure Sales QTD = CALCULATE([Sales Amount], DATESQTD(‘Date‘[Date]))
Do the above steps help you to calculate the QTD sales values?

Select 1 option(s):

You have to calculate the Quarter-to-date sales values from the Sales table. Below is the given data model.

Solution: You perform the following steps:
a. Create a relationship between the Date Key column in the Date table and the Order Date Key column in the Sales table
b. You do not Mark the Date table as a date table.
c. You create the measure Sales QTD = CALCULATE([Sales Amount], DATESQTD(‘Date‘[Date]))
Do the above steps help you to calculate the QTD sales values?

Select 1 option(s):

You have to calculate the Quarter-to-date sales values from the Sales table. Below is the given data model.

Solution: You perform the following steps:
a. Create a relationship between the Date Key column in the Date table and the Order Date Key column in the Sales table
b. You do not mark the Date table as a date table.
c. You create the measure
Sales QTD =
CALCULATE (
[Sales Amount],
DATESQTD ( ‘Date‘[Date] ),
REMOVEFILTERS ( ‘Date‘ )
)
Do the above steps help you to calculate the QTD sales values?

Select 1 option(s):

Which of the following would you do/create?

Select 1 option(s):

What does 2.57x in the green circle indicate?

Select 1 option(s):