Microsoft Power BI Test - 16

Welcome to your Microsoft Power BI – Test-16

Name

Phone

Q1. You create the below data model in Power BI.

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

Set the cross filter direction between the Accounts and Account Types table to Both
Check Apply security filter in both directions

Set the cross filter direction between the Permissions and Account Types table to Both
Check Apply security filter in both directions

Change the relationship cardinality from Permissions -> Account Types to One to many
Set the Cross filter direction between the Permissions and Account Types table to Both

Change the relationship cardinality from Permissions -> Account Types to One to many
Check Apply security filter in both directions

Q2. You monitor an active fraudulent activity report in the Power BI service that connects to a backend financial data mart. The Teradata Database is updated whenever customers use a credit card. You have to detect counterfeit transactions as soon as they happen.

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

Select 1 option(s):

Use DirectQuery as Data Connectivity mode with reports on shared capacity

Use Import as Data Connectivity mode with reports on Premium capacity

Use Import as Data Connectivity mode with reports on shared capacity

Use DirectQuery as Data Connectivity mode with reports on Premium capacity

Q3. Koovs is an online e-commerce store based in India that sells womens 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.

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

Scatter chart

Line chart

Stacked bar chart

Bubble chart

Q4. 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 bulk orders received from each City and Sales channel. Which of the following options would you choose?

Select 1 option(s):

Create a Basic Group by transform. And set the Group by operation to Count Rows

Create an Advanced Group by transform. And set the Group by operation to Count Rows

Create a Basic Group by transform. And set the Group by operation to Count Distinct Rows

Create an Advanced Group by transform. And set the Group by operation to Count Distinct Rows

Q5. 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 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):

Yes

Q6. 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 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):

Yes

Q7. This question is part of repeated scenario questions that contain a similar 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 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 customers 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):

Yes

Q8. This question is part of repeated scenario questions that contain a similar 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.

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

Yes

Q9. Standard Motor Products is a leading manufacturer of replacement parts in the aftermarket industry. You build the below data model to help their sales managers understand the sales performance of automotive products.

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

Option1: CALCULATETABLE
Option 2: SUM
Option 3: RANKX
Option 4: Sales [Sales Amount]

Option1: EVALUATE
Option 2: SUMMARIZE
Option 3: TOPN
Option 4: Sales [Sales ID]

Option1: CALCULATE
Option 2: SUM
Option 3: TOPN
Option 4: Sales [Order Date Key]

Option1: FILTER
Option 2: SUMX
Option 3: RANKX
Option 4: Sales [Sales Amount]

Q10. You enable users to naturally interact with your Power BI report using the Q&A feature. You create a Date hierarchy (contains Year and Month) in the Date table to visualize Affiliate Income.

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 add a synonym to the Date hierarchy

You add a description to the Date hierarchy

You add a synonym to the Date table, as you cannot add a synonym to a hierarchy

You add a synonym to the year and month fields of the hierarchy

Q11. You are analyzing a Power BI data model with only a Sales table. From the Order Date hierarchy, you can analyze the sales values by Year, Month, and Day.

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

Select 1 option(s):

Create a new group with Group type set to ‘Bin‘ and Bin Type set to ‘Number of bins‘

Create a new Date table with CALENDARAUTO and WEEKNUM function

Create a new group with Group type set to ‘List‘

Create a new group with Group type set to ‘Bin‘ and Bin Type set to ‘Size of bins‘

Q12. You plot an R visual that maps the global locations of active COVID-19 cases in Power BI.

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

The script calculating the R visual times out

The R script encounters an error

The R script failed to run correctly due to a missing R package

Data size limitations for the R visual is exceeded

Q13. You have a clustered column chart that displays Units Sold by Year and Country.

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

By creating several column charts

By creating small multiples

By creating Sparklines

By creating Bookmarks

Q14. You perform transformations in Power Query to import table rows of only the top 40 frequently purchasing customers from a fact table in SQL Server.

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

Select 1 option(s):

View Native Query option

Query Diagnostics

Performance Analyzer

SQL Server Profiler

Q15. You need to test your reports in multiple environments like Dev, UAT & Prod (DTAP street). The data source for each environment is on a different physical machine running a separate instance of SQL Server 2019.

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

Select 1 option(s):

Create Power BI variables

Create Power BI what-if parameters

Create Power Query parameters

In the Power Query M code, replace the references to the server

Q16. In the Power BI Desktop, we have an imported table that details a list of Ordered Products for each Invoice Number (shown below).

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

Select 1 option(s):

Pivot the table

Split the column

Unpivot the table

Transpose

Q17. Below is the Key influencer chart for customer feedback provided by Microsoft.

What does the dotted Average line indicate?

Select 1 option(s):

Percentage of Administrators, Consumers, and Publishers who gave low ratings

Percentage of low ratings given by Administrator and Publishers

Percentage of low ratings given by Administrator, Consumers, and Publishers

Percentage of Administrators and Publishers who gave low ratings

Q18. You are working with the Wide World Importers Data Warehouse. You need to do the following:

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

You write a native SQL query during import.

You use the Power Query editor to filter the data.

You create a DAX expression with the FILTER function.

You use Power BI report-level filters.

Q19. Microsoft stock is traded on the NASDAQ stock exchange, operational on all days except weekends and market holidays.

Below is a data model with details of daily Microsofts 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):

Month Closing Price =
CALCULATE ( SUM ( ‘MS Stock Prices‘[Close] ), LASTDATE ( ‘Date‘[Date] ) )

Month Closing Price = CALCULATE ( SUM ( ‘MS Stock Prices‘[Close] ), LASTNONBLANK ( ‘Date‘[Date], [MS Close Price] ) )

Month Closing Price =
CALCULATE (
[MS Close Price],
LASTNONBLANK ( ‘Date‘[Date], SUM ( ‘MS Stock Prices‘[Close] ) )
)

Month Closing Price =
CALCULATE ( [MS Close Price], LASTDATE ( ‘Date‘[Date] ) )

Q20. You (a developer) build and deploy a production analytics dashboard for a manufacturing team to analyze the operational efficiency metrics of the product lines.

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

Build a data flow for the Product table

Copy the M query from the PBIX file used to build the production analytics dashboard

Use the shared dataset of the production analytics report/dashboard

Create a Power BI datamart

Q21. You analyze sales data of a sports equipment company. You observe a lot of fluctuations in the data, such as low sales mid-week and high sales on Friday and Saturday.

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

Days, 7, 7

Days, 6, 6

Days, 6, 0

Days, 7, 0

Days 7, 1

Q22. You create the below KPI visual in Power BI to understand your sales teams performance compared to their sales target this year.

What does the value 19462 indicate?

Select 1 option(s):

Average sales value for all the years

Sales value for all the years

Average sales value for all the previous years

Sales value for the current year

Q23. Your HR team gives you a dataset with a list of employees who left/joined the organization last year since its beginning on Jan 1, 2020.

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

Waterfall chart

Line chart

Gantt chart

Stacked area chart

Q24. You are a new Power BI developer hired by your company. You need to develop a table matrix visual that displays the organizations last years Revenue for each month. Which of the following DAX formulas would you use?

(Select 2 options)

Select 2 option(s):

CALCULATE ( [Revenue], DATEADD ( ‘Date‘[Date], -1, YEAR ) )

CALCULATE ( [Revenue], PARALLELPERIOD ( ‘Date‘[Date], -1, YEAR ) )

CALCULATE ( [Revenue], PREVIOUSYEAR (‘Date‘[Date] ) )

CALCULATE ( [Revenue], SAMEPERIODLASTYEAR ( ‘Date‘[Date] ) )

Q25. There is a table visual that displays change in Quarter-over-Quarter sales value in percentage terms (Sales Last Year QoQ%). In some quarters, the sales has decreased compared to the same quarter the previous year, and so QOQ percentage values are negative.

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

Use data bars to color code a cell

Add a column with a list of color names. And use this column to apply conditional formatting

Apply a background conditional formatting with the Format style set as the Field value

Apply the same conditional formatting to a fields font & background color

Q26. Below is a line chart that plots Apple stocks daily closing price for the past five years and the 200-Day Simple Moving Average (200 DMA). Some traders use 200 DMA to enter/exit positions in a stock.

As a trader, if Apples stock price goes above 200 DMA, you enter a position. If Apples 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):

Create a Subscription

Create a data alert

Integrate Power BI notifications with Power Automate

Its not possible to achieve the goal with the above options