Microsoft Power BI Certification (PL-300) Practice Test 9

A career switch fails when the only proof is a course screenshot. Hiring managers want messy business data turned into a decision. Power BI is the tool many teams hand you on day one because connection, modeling, and visualization live in one product family. Data analysts own that build cycle. Data scientists step in when the visual must explain a prediction or a what-if. The PL-300 certification gives career changers a third-party check beside the portfolio and a clearer LinkedIn signal. Use this Free Microsoft Power BI Data Analyst Associate Certification (PL-300) Practice Test 9 as a timed rep, then fix the gaps it reveals. Consumer brands that hire this profile include Nike, Starbucks, and Procter & Gamble. U.S. base pay varies by city and industry: junior data analysts often earn $58,000–$75,000, mid-level analysts commonly earn $78,000–$108,000, and senior data analysts frequently reach $112,000–$145,000. Synergisticit JOPP helps jobseekers get certified, prepared, and hired at top tech companies through our Job Placement programs—Java DevOps Full Stack JOPP and Data Science JOPP.

If you want to learn how to get hired as a Power BI Data Analyst, Data Scientist, Data Engineer, AI or ML engineer, Java developer, or software engineer, contact Synergisticit for expert upskilling and marketing connections to companies. Visit Synergisticit Contact Us and Synergisticit JOPP.

Employers expect professionals to master Tableau, SQL, SAS, and Azure, while also gaining data engineering expertise in Snowflake, Databricks, and PySpark. Data science and AI skills in TensorFlow, PyTorch, LLMs, and Generative AI are equally critical. Project work is the ultimate proof of employability.

The SynergisticIT Data Science Job Placement Program ensures your confidence translates into career readiness. Explore more through the SynergisticIT Job Placement Program.

Welcome to your Microsoft Power BI - Test-9

Name

Phone

Q1. You have a Microsoft Power BI data model that contains three tables named Orders, Date, and City. There is a one-to-many relationship between Date and Orders and between City and Orders. The model contains two row-level security (RLS) roles named Role1 and Role2.

Role1 contains the following filter. City[State Province] = “Minnesota”
Role2 contains the following filter. Date[Calendar Year] = 2020
If a user is a member of both Role1 and Role2, what data will they see in a report that uses the model?

Select 1 option(s):

The user will see data for which the State Province value is Minnesota and the Calendar Year is 2020

The user will see data for which the State Province value is Minnesota or the Calendar Year is 2020.

The user will see only data for which the State Province value is Minnesota

The user will receive an error and will not be able to see the data in the report.

Q2. You have multiple dashboards. You need to ensure that when users browse the available dashboards from powerbi.com. they can see which dashboards contain Personally Identifiable Information (Pll). The solution must minimize configuration effort and impact on the dashboard design.

Select 1 option(s):

data classifications

Active Directory groups

Tiles

Comments

Q3. You are developing a sales report that will have multiple pages. Each page will answer a different business question.

You plan to have a menu page that will show all the business questions. You need to ensure that users can click each business question and be directed to the page where the question is answered.
The solution must ensure that the menu page will work when deployed to any workspace.
What should you include on the menu page?

Select 1 option(s):

Create a text box for each business question and insert a link

Create a button for each business question and set the action type to Bookmark.

Create a Power Apps visual that contains a drop-down list. The drop-down list will contain the business questions.

Q4. You create a dashboard by using the Microsoft Power Bl Service. The dashboard contains a card visual that shows total sales from the current year. You grant users access to the dashboard by using the viewer role in the workspace. A user wants to receive daily notifications of the number shown on the card visual.

What should the user can do?

Select 1 option(s):

Create a data alert.

Share the dashboard to the user.

Create a subscription.

Q5. You build a report to help the sales team understand its performance and the drivers of sales. The team needs to have a single visualization to identify which factors affect success. Which type of visualization should you use?

Select 1 option(s):

Line and clustered column chart

Key influencer

Funnel chart

Q6. You have four sales regions. Each region has multiple sales managers.

You implement row-level security (RLS) in a data model. You assign the relevant distribution lists to each role.
You have sales reports that enable analysis by region. The sales managers can view the sales records of their region. The sales managers are prevented from viewing records from other regions. A sales manager changes to a different region. You need to ensure that the sales manager can see the correct sales data
What should you do?

Select 1 option(s):

Change the Microsoft Power BI license type of the sales manager.

From Microsoft Power BI Desktop, edit the Row-Level Security setting for the reports.

Request that the sales manager be added to the correct Azure Active Directory group.

Manage the permissions of the underlying dataset.

Q7. You are configuring a Microsoft Power BI data model to enable users to ask natural language questions by using Q&A.

You have a table named Customer that has the following measure.
Customer Count = DISTINCTCOUNT(Customer[CustomerID])
Users frequently refer to customers as subscribers. You need to ensure that the users can get a useful result for “subscriber count” by using Q&A.
The solution must minimize the size of the model.
What should you do?

Select 1 option(s):

Add a synonym of "subscriber" to the Customer table.

Add a synonym of "subscriberID" to the CustomerID column.

Add a description of "subscriber count" to the Customer Count measure.

Set Summarize By to None for the CustomerID column.

Q8. You use an R visual to produce a map of 500,000 customers. You include the values of [CustomerID], Latitude, and Longitude in the fields sent to the visual. Each customer ID is unique.

In powerbi.com, when users load the visual, they only see some of the customers.
What is the cause of the issue?

Select 1 option(s):

The visual was built by using a different version of R

Too many records were sent to the visual.

The data comes from a Microsoft SQL Server source.

The data is deduplicated.

Q9. You have a Microsoft Power Bl dashboard. You need to ensure that consumers of the dashboard can give you feedback that will be visible to the other consumers of the dashboard.

What should you use?

Select 1 option(s):

Comments

Mark as favorite

Q10. You have a Microsoft SharePoint Online site that contains several document libraries. One of the document libraries contains manufacturing reports saved as Microsoft Excel files. All the manufacturing reports have the same data structure. You need to load only the manufacturing reports to a table for analysis. What should you do on Microsoft Power Bl Desktop?

Select 1 option(s):

Get data from a SharePoint Online list, enter the site URL and then select Combine & Load

Get data from a SharePoint Online folder and enter the site URL. Edit the query and filter by folder path.

Get data from a SharePoint Online folder, enter the site URL, and then select Combine & Load

Get data from a SharePoint Online list and enter the site URL. Edit the query and filter by folder

Q11. You have several reports and dashboards in a workspace. You need to grant all organizational users to read access to a dashboard and several reports.

Solution: You publish an app to the entire organization.
Does this meet the goal?

Select 1 option(s):

Yes

Q12. You need to grant all organizational users to read access to a dashboard and several reports.

Solution: You create an Azure Active Directory group that contains all the users. You share each report and dashboard with the group.
Does this meet the goal?

Select 1 option(s):

Yes

Q13. You have several reports and dashboards in a workspace. You need to grant all organizational users to read access to a dashboard and several reports.

Solution: You assign all the users the Viewer role to the workspace.
Does this meet the goal?

Select 1 option(s):

Yes

Q14. You publish a Microsoft Power BI dataset to powerbi.com. The dataset appends data from an on-premises Oracle database and an Azure SQL database by using one query. You have admin access to the workspace and permission to use an existing On-premises data gateway for which the Oracle data source is already configured. You need to ensure that the data is updated every morning. The solution must minimize configuration effort.

Which two actions should you perform when you configure scheduled refresh? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Select 2 option(s):

Deploy an On-premises data gateway in personal mode.

Set the refresh frequency to Daily.

Configure the dataset to use the personal gateway.

Configure the dataset to use the existing On-premises data gateway.

Q15. You have a report that contains three pages. One of the pages contains a KPI visualization. You need to filter all the visualizations in the report except for the KPI visualization. Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point

Select 2 option(s):

Add the same slicer to each page and configure Sync slicers.

Edit the interactions of the KPI visualization.

Configure a page-level filter

Edit the interactions of the slicer that is on the same page as the KPI visualization.

Configure a report-level filter.

Q16. You have the tables shown in the following table.

The Impressions table contains approximately 30 million records per month.
You need to create an ad analytics system to meet the following requirements:
* Present ad impression counts for the day, campaign, and [Site_name]. The analytics for the last year is required.
* Minimize the data model size.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Select 2 option(s):

Create a calculated measure that aggregates by using the COUNTROWS function.

Create a calculated table that contains Ad_id, Site_name, and Impression_date.

Group the impressions by Ad_id, Site_name, and Impression_date. Aggregate by using the CountRows function.

Create one-to-many relationships between the tables.

Q17. You are creating a Microsoft Power BI data model that has the tables shown in the following table.

The Products table is related to the ProductCategory table through the [ProductCategoryID] column.
You need to ensure that you can analyze sales by product category.
How should you configure the relationships from Products to ProductCategory? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Select 1 option(s):

Box 1: Many to Many Box 2: Both

Box 1: One to Many Box 2: Both

Box 1: One to One Box 2: Single

Box 1: Many to One Box 2: Single

Q18. You have a dataset named Pens that contains the following columns:

*Unit Price
*Quantity Ordered
You need to create a visualization that shows the relationship between Unit Price and Quantity Ordered. The solution must highlight orders that have a similar unit price and ordered quantity.
Which type of visualization and which feature should you use? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Select 1 option(s):

Box 1: A line chart of Quantity Ordered and Unit Price by item Box 2: Find where the distribution is different

Box 1: A scatter plot of Quantity Ordered and Unit price by item Box 2: Automatically find clusters

Box 1: A column chart of Quantity Ordered and Unit Price by year Box 2: Automatically find clusters

Q19. DRAG and DROP

You are modeling data in a table named SalesDetail by using Microsoft Power Bl. You need to provide end-users with access to the summary statistics about the SalesDetail data. The users require insights on the completeness of the data and the value distributions. 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

Select 1 option(s):

Box 1: Create a blank query as a data source Box 2: Specify the following query. =Table.Distinct(#”SaleDetail”) Box 3: Create a visual for the query table

Box 1: Create a blank query as a data source Service Box 2: Specify the following query. =Table.Profile(#”SaleDetail”) Box 3: Create a visual for the query table

Box 1: Create a parameter that uses Common Data Service as a data source Box 2: Specify the following query. =Table.Profile(#”SaleDetail”) Box 3: Create a visual for the query table

Q20. You have five sales regions. Each region is assigned a single salesperson.

You have an imported dataset that has a dynamic row-level security (RLS) role named Sales. The Sales role filters sales transaction data by the salesperson. Salespeople must see only the data from their region.
You publish the dataset to powerbi.com, set RLS role membership, and distribute the dataset and related reports to the salespeople.
A salesperson reports that she believes she should see more data.
You need to verify what data the salesperson currently sees.
What should you do?

Select 1 option(s):

Use the Test as role option to view data as the salesperson's user account

Filter the data in the reports to match the intended logic in the filter on the sales transaction table

Instruct the salesperson to open the report in Microsoft Power Bl Desktop

Use the Test as role option to view data as the Sales role.

Q21. DRAG and DROP

You are using existing reports to build a dashboard that will be viewed frequently in portrait mode on mobile phones.
You need to build the dashboard.
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:

Select 1 option(s):

1. Open Dashboard 2. Create a phone layout of existing reports 3. Change the dashboard view to phone view 4. Rearrange, resize, or remove items from the phone view 1. Open Dashboard 2. Create a phone layout of existing reports 3. Change the dashboard view to phone view 4. Rearrange, resize, or remove items from the phone view

1. Pin items from the reports to the dashboard 2. Open the dashboard 3. Change the dashboard view to phone view 4. Rearrange, resize, or remove items from the phone view

1. Open the dashboard 2. Change the dashboard view to phone view 3. Create a phone layout of existing reports 4. Rearrange, resize, or remove items from the phone view

Q22. You have a Microsoft Power Bl workspace. You need to grant the user capabilities shown in the following table.

The solution must use the principle of least privilege. Which user role should you assign to each user?
Admin
Contributor
Member
Viewer

Select 1 option(s):

User 1: Member User 2: Admin

User 1: Member User 2: Contributor

User 1: Admin User 2: Contributor

Q23. You need to create a visual as shown in the following exhibit

The indicator color for Rolling 12 Denied is based on Prior rolling 12.

Select 1 option(s):

Conditional Formatting: Background color Format by : Color scale

Conditional Formatting: Font color Format by : Color scale

Conditional Formatting: Font color Format by : Rules

Conditional Formatting: Background color Format by : Field value

Conditional Formatting: Background color Format by : Rules

Q24. What type of expression supported by Power BI while extracting data?

Select 1 option(s):

SQL, MDX, DAX

SQL, Python, R

SQL, DAX, M

Q25. You have a sales system that contains the tables shown in the following table.

The Date table is marked as a date table. DatelD is the date data type. You need to create an annual sales growth percentage measure. Which DAX expression should you use?

Select 1 option(s):

CALCULATE(SUM(sales[sales_amount), SAMEPERIODLASTYEAR('Date'[DatelD])

SUM(sales[sales_amount]) - CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR('Date'[DatelD]))/ CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR ('Date'[DateID])

CALCULATE(SUM(sales[sales_amount]), DATESYTD('Date'[DatelD]))

SUM(sales[sales_amount]) - CALCULATE(SUM(sales[sales_amount]), SAMEPERIODLASTYEAR('Date'[DatelD])

Q26. You have the visual shown below-

What is the name of the visual?

Select 1 option(s):

Decomposition tree visual

Key Influencer

Breaking down custom visual

Horizonal bar chart

Q27. How do you use an inactive relationship in a single measure?

Select 1 option(s):

Change the active relationship in the Modeling tab.

Delete one relationship.

Use the USERELATIONSHIP function.

Q28. You receive revenue data from Contoso that must be included in Microsoft Power Bl reports.

You perform an initial load of the data from a Microsoft Excel source as shown in the below image

You plan to create several visuals from the data, including a visual that shows revenue split by year and product. You need to transform the data to ensure that you can build the visuals.

Select 1 option(s):

Box 1 : Select Use First rows as header Box 2: Select Department and Product and Unpivot columns Box 3: Rename the third column as Year and Fourth Column as Revenue

Box 1 : Select Use Header as First row Box 2: Select Department and Product and Unpivot columns Box 3: Rename the third column as Year and Fourth Column as Revenue

Box 1 : Select Use First rows as header Box 2: Select Department and product and Unpivot Other columns Box 3: Rename the third column as Year and Fourth Column as Revenue

Q29. You are creating a Microsoft Power Bl imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories.

You import a fact table named Sales as shown –

The related dimension tables are imported into the model.
The SalesRowID and AuditID can be removed from the model without impeding the analysis goals?

Select 1 option(s):

Yes

Q30. You are creating a Microsoft Power Bl imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories.

You import a fact table named Sales as shown –

The related dimension tables are imported into the model.
Both the OrderDateKey and OrderDate columns are necessary to perform the basket analysis?

Select 1 option(s):

Yes

Q31. You are creating a Microsoft Power Bl imported data model to perform basket analysis. The goal of the analysis is to identify which products are usually bought together in the same transaction across and within sales territories.

You import a fact table named Sales as shown –

The related dimension tables are imported into the model.
The TaxAmt column must retain the current number of decimal places to perform the basket analysis?

Select 1 option(s):

Yes

Q32. You need to calculate the last day of the month in the balance sheet data to ensure that you can relate the balance sheet data to the Date table. Which type of calculation and which formula should you use?

To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Select 1 option(s):

Type of Calculation: A DAX Calculated column Formula : ENDOFQUARTER(DATE('BalancedSheet'[Year],BalancedSheet'[Month],1),0)

Type of Calculation: A DAX Calculated measure Formula : ENDOFQUARTER(DATE('BalancedSheet'[Year],BalancedSheet'[Month],1),0)

Type of Calculation: A M query custom column Formula : Date.EndOfMonth(#date[year],[month],1))

Q33. Consider the data from the Adventure Works bicycle company.

In the below screenshot, What will be the Impact on multi-card visual (Visual 1) and Column chart visual (Visual 3) If I make a selection of Unites States in a bar chart (Visual 2)?

Select 1 option(s):

Visual 1: No impact Visual 3: Cause filter interaction

Visual 1: No impact Visual 3: Cause Cross filter interaction

Visual 1: No impact Visual 3: No impact

Visual 1: Cause filter interaction Visual 3: No Impact

Q34. Litware, Inc. Case Study

This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
-Overview
Litware, Inc. is an online retailer that uses Microsoft Power Bl dashboards and reports.
The company plans to leverage data from Microsoft SQL Server databases, Microsoft Excel files, text files, and several other data sources.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
– Existing Environment Sales Data
Litware has online sales data that has the SQL schema shown in the following table.

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question:
You need to create the required relationship for the executive’s visual. What should you do before you can create the relationship?

Select 1 option(s):

Change the data type of sales[sales_id] to Text.

Change the data type of sales [region_id] to Decimal Number

Change the data type of Sales[region_id] to Whole Number.

In the Sales table, add a measure for sum(sales_amount).

Q35. Litware, Inc. Case Study

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-

What should you create to meet the reporting requirements of the sales department?

Select 1 option(s):

a calculated column that use a formula of COUNTA(Sales[sales_id])

a calculated measure that uses a formula of COUNTROWS(Sales)

a calculated column that uses a formula of SUM(Sales[sales_id])

a measure that uses a formula SUM(Sales[sales_id])

Q36. Litware, Inc. Case Study

Litware, Inc. Case Study
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
-Overview
Litware, Inc. is an online retailer that uses Microsoft Power Bl dashboards and reports.
The company plans to leverage data from Microsoft SQL Server databases, Microsoft Excel files, text files, and several other data sources.
Litware uses Azure Active Directory (Azure AD) to authenticate users.
– Existing Environment Sales Data
Litware has online sales data that has the SQL schema shown in the following table.

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-
You need to create relationships to meet the reporting requirements of the customer service department.
What should you create?

Select 1 option(s):

a one-to-many relationship from Date[date_id] to Sales[sales_date_id] and another one-to-many relationship from Date[date_id] to Weekly_Returns[week_id]

a one-to-many relationship from Sales[sales_date_id] to Date[date_id] and a one-to-many relationship from Sales[sales_ship_date_id] to Date[date_id]

a one-to-many relationship from Date[date_id] to Sales[sales_date_id] and another one-to-many relationship from Date[date_id] to Sales[sales_ship_date_id]

an additional date table named ShipDate, a one-to-many relationship from Sales[sales_date_id] to Date[date_id], and a one-to-many relationship from Sales[sales_ship_date_id] to ShipDate[date_id]

Q37. Litware, Inc. Case Study

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-
You need to create a relationship between the Weekly_Returns table and the Date table to meet the reporting requirements of the regional managers. What should you do?

Select 1 option(s):

Add the Weekly_Returns data to the Sales table by using related DAX functions.

Create a new table based on the Date table where date-id is unique, and then create a many-to-many relationship to Weekly_Return.

In the Weekly_Returns table, create a new calculated column named date-id in a format of yyyymmdd and use the calculated column to create a relationship to the Date table.

Q38. Litware, Inc. Case Study

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-
You need to create a calculated column to display the month based on the reporting requirements. Which DAX expression should you use?

Select 1 option(s):

FORMAT('Date'[date], "MMM YYYY")

FORMAT('Date' [date], "M YY")

FORMAT('Date'[date_id], "MMM") & "" & FORMAT('Date'[year], "#")

FORMAT('Date' [date_id], "MMM YYYY")

Q39. Litware, Inc. Case Study

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-
You need to address the data concerns before creating the data model. What should you do in Power Query Editor?

Select 1 option(s):

Transform the sales_amount column to replace negative values with 0.

Select Column distribution.

Select Column profile, and then select the sales_amount column.

Q40. Litware, Inc. Case Study

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-
You need to provide a solution to provide the sales managers with the required access. What should you include in the solution?

Select 1 option(s):

Create a security role that has a table filter on the Sales_Manager table where name = UserName().

Create a security role that has a table filter on the Sales_Manager table where username = sales_manager_id.

Create a security role that has a table filter on the Sales_Manager table where username = UserName()

Create a security role that has a table filter on the Region_Manager table where sales_manager_id = UserPrincipalName().

Q41. Litware, Inc. Case Study

In the Date table, the date column has a format of yyyymmdd and the month column has a format of yyyymm. The week column in the Date table and the week_id column in the Weekly_Returns table have a format of yyyyww. The region_id column can be managed by only one sales manager.
Data Concerns
You are concerned with the quality and completeness of the sales data. You plan to verify the sales data for negative sales amounts.
Reporting Requirements
Litware identifies the following technical requirements:
* Executives require a visual that shows sales by region.
* Regional managers require a visual to analyze weekly sales and returns.
* Sales managers must be able to see the sales data of their respective regions only.
* The sales managers require a visual to analyze sales performance versus sales targets.
* The sales department requires reports that contain the number of sales transactions.
*Users must be able to see the month in reports as shown in the following example: Oct 2020.
*The customer service department requires a visual that can be filtered by both sales month and ship month independently.
Question-
You need to create a visualization to meet the reporting requirements of the sales managers. How should you create the visualization? To answer, select the appropriate options in the answer

Select 1 option(s):

1. Visualization type: KPI 2. Indicator: Sales[sales_amount] 3. Trend axis: Date[month] 4. Target goals: Targets[Sales_target]

1. Visualization type: Gauge 2. Indicator: Sales[sales_amount] 3. Trend axis: Date[month] 4. Target goals: Targets[Sales_target]

1. Visualization type: Gauge 2. Indicator: Targets[Sales_target] 3. Trend axis: Date[month] 4. Target goals: Sales[sales_amount]

1. Visualization type: Donut chart 2. Indicator: Targets[Sales_target] 3. Trend axis: Date[month] 4. Target goals: Sales[sales_amount]

Q42. Contoso, Ltd. is a manufacturing company that produces outdoor equipment. Contoso has quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including profit and loss statements for each of the company’s four business units, a company balance sheet, and net income projections for the next quarter.

Data and Sources
Data for the reports come from three sources. Detailed revenue, cost, and expense data come from an Azure SQL database. Summary balance sheet data comes from Microsoft Dynamics 365 Business Central. The balance sheet data is not related to the profit and loss results, other than they both relate dates.
Monthly revenue and expense projections for the next quarter come from a Microsoft SharePoint Online list. Quarterly projections relate to the profit and loss results by using the following shared dimensions: date, business unit, department, and product category.
Net Income Projection Data
Net income projection data is stored in a SharePoint Online list named Projections in the format shown in the following table.

Revenue projections are set at the monthly level and summed to show projections for the quarter.
Balance Sheet Data
The balance sheet data is imported with final balances for each account per month in the format shown in the following table.

There is always a row for each account for each month in the balance sheet data.
Dynamics 365 Business Central Data
Business Central contains a product catalog that shows how products roll up to product categories, which roll up to business units.
Revenue data is provided at the date and product level. Expense data is provided at the date and department level.
Business Issues
Historically, it has taken two analysts a week to prepare the reports for the quarterly board meetings. Also, there is usually at least one issue each quarter where a value in a report is wrong because of a bad cell reference in an Excel formula. On occasion, there are conflicting results in the reports because the products and departments that roll up to each business unit are not defined consistently.
Requirements:-
1. Planned Changes -Contoso plans to automate and standardize the quarterly reporting process by using Microsoft Power BI. The company wants to how long it takes to populate reports to less than two days. The company wants to create common logic for business units, products, and departments to be used across all reports, including, but not limited, to the quarterly reporting for the board.
2. Technical Requirements -Contoso wants the reports and datasets refreshed with minimal manual effort. The company wants to provide a single package of reports to the board that contains custom navigation and links to supplementary information. Maintenance, including manually updating data and access, must be minimized as much as possible.
3. Security Requirements – The reports must be made available to the board from powerbi.com. A mail-enabled security group will be used to share information with the board. The analysts responsible for each business unit must see all the data the board sees, except the profit and loss data, which must be restricted to only their business unit’s data. The analysts must be able to build new reports from the dataset that contains the profit and loss data, but any reports that the analysts build must not be included in the quarterly reports for the board. The analysts must not be able to share the quarterly reports with anyone.
4.Report Requirements – You plan to relate the balance sheet to a standard date table in Power BI in a many-to-one relationship based on the last day of the month. At least one of the balance sheet reports in the quarterly reporting package must show the ending balances for the quarter, as well as for the previous quarter. Projections must contain a column named RevenueProjection that contains the revenue projection amounts.

The relationships between products and departments to business units must be consistent across all reports.
The board must be able to get the following information from the quarterly reports:
– Revenue trends over time
– Ending balances for each account
– A comparison of expenses versus projections by a quarter
– Changes in long-term liabilities from the previous quarter
– A comparison of quarterly revenue versus the same quarter during the prior year
Question:-
How should you distribute the reports to the board? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Select 1 option(s):

Box 1: Using a workspace membership Box 2: A mail-enabled security group

Box 1: Using an app Box 2: A mail-enabled security group

Box 1: Using an app Box 2: A dynamic distribution list

Box 1: Using a workspace membership Box 2: A dynamic distribution list

Q43. Contoso, Ltd. is a manufacturing company that produces outdoor equipment. Contoso has quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including profit and loss statements for each of the company’s four business units, a company balance sheet, and net income projections for the next quarter.

The relationships between products and departments to business units must be consistent across all reports.
The board must be able to get the following information from the quarterly reports:
– Revenue trends over time
– Ending balances for each account
– A comparison of expenses versus projections by a quarter
– Changes in long-term liabilities from the previous quarter
– A comparison of quarterly revenue versus the same quarter during the prior year
Question:-
What is the minimum number of datasets and storage modes required to support the reports?

Select 1 option(s):

a single imported dataset

a single DirectQuery dataset

two DirectQuery datasets

two imported datasets

Q44. Contoso, Ltd. is a manufacturing company that produces outdoor equipment. Contoso has quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including profit and loss statements for each of the company’s four business units, a company balance sheet, and net income projections for the next quarter.

The relationships between products and departments to business units must be consistent across all reports.
The board must be able to get the following information from the quarterly reports:
– Revenue trends over time
– Ending balances for each account
– A comparison of expenses versus projections by a quarter
– Changes in long-term liabilities from the previous quarter
– A comparison of quarterly revenue versus the same quarter during the prior year
Question:-
Users want a column like Apr-20 for reporting purposes. How you will create this?

Select 1 option(s):

FORMAT (Date[Date], "MMM- YYYY")

FORMAT (Date[Date], "MM -YYYY")

FORMAT (Date[Date], "MMM -YY")

FORMAT (Date[Date], "M- YYYY")

Q45. Contoso, Ltd. is a manufacturing company that produces outdoor equipment. Contoso has quarterly board meetings for which financial analysts manually prepare Microsoft Excel reports, including profit and loss statements for each of the company’s four business units, a company balance sheet, and net income projections for the next quarter.

The relationships between products and departments to business units must be consistent across all reports.
The board must be able to get the following information from the quarterly reports:
– Revenue trends over time
– Ending balances for each account
– A comparison of expenses versus projections by a quarter
– Changes in long-term liabilities from the previous quarter
– A comparison of quarterly revenue versus the same quarter during the prior year
Question:-
How you will create a custom Date Calendar?

Select 1 option(s):

CALENDARAUTO() Date = ADDCOLUMNS ( CALENDAR (DATE(2000,1,1); DATE(2020,12,31)); "Month"; FORMAT ( [Date]; "MM" ); "Month Name"; FORMAT ( [Date]; "MMM" ); "Quarter"; "Q" & FORMAT ( [Date]; "Q" ); "Year"; YEAR ( [Date] ) )

CALENDAR ("2000 01 01 ","2020 12 31")

Q46. You use Power BI Desktop to prepare data. You examine data quality in Power Query.

You need to discover the number of errors in a column.
Which two Data preview options should you use? Each correct answer presents a complete solution.

Select 2 option(s):

Show whitespace

Column profile

Column quality

Column distribution

Q47. What is the use of “Select Related Table” option while importing data from SQL Server?

Select 1 option(s):

It will also import views related to selected tables

It won't import any extra table or view.

It will import tables that have a physical relationship with the selected table.

It will import the tables that have similar schema

Q48. You are creating a dataset and modeling the data for a Power Bl report. The report should contain up-to-date information. The tables in the data model are shown in the exhibit and are described below:

• Sales has the details of sales made to customers and has a large number of records. Many new records are added daily.
• Customer is the list of customers. Customers have a few thousand records and there are additions and updates regularly.
• Territory is the geographical area for the sales teams. Territories are rarely updated.
• Date is a date table created with DAX.
You need to select the correct storage mode to meet your company’s requirements.
Which storage mode should you use for the following requirement?
“Use Q&A in reports”

Select 1 option(s):

Live Connection

Dual

Import

DirectQuery

Q49. “Live Connection is used when connecting to multidimensional data sources, such as Analysis Services”

Select whether the above statement is True or False.

Select 1 option(s):

TRUE

FALSE

Q50. You import an Excel spreadsheet into Power Bl Desktop. The spreadsheet contains manufacturer, model, and specifications of the computers deployed in your company.

You need to examine the quality of the data. The results of Data Preview are shown in the below images.

For the following statement, select Yes if the statement is true. Otherwise, select No.
“The DeviceID column is a candidate for the primary key for this table.”

Select 1 option(s):

Yes

Q51. You plan to use dataflows to perform extract, transform, and load processing on data stored in the Common Data Service to prepare it for Power Bl reports.

You need to set up the dataflows to process and store data. Which components should you use for the following process?
“Store data in dataflows using….”

Select 1 option(s):

JSON files

Common Data Model folders

OneDrive for Business

Q52. Overview:

You work for a company that uses Power Bl for financial reporting. Analysis and insights for sales are now required.
Existing Environment:
Power Bl service is used and workspaces have been created. Power Bl Desktop is installed.
Data Sources:
Sales data is stored in an Azure SQL database. Dimension data for Territory and Country is held in an Excel workbook in SharePoint Online.
Sales schema – The schema for the sales data is shown in the Schema exhibit. Product Ids are alphanumeric and consist of three letters followed by three numbers. The Sales amount is net of any taxes The Tax rate is 20 percent on all sales.
Territory workbook – The data for territories is shown in the Territory workbook exhibit.
Issues:
You observe the following issues:
The labels for country are not consistent for territories.
The labels used are not the commonly used names in your company.
The labels do not follow naming corporate standards with capitalization, no special characters, and no abbreviations.
Users must be able to analyze sales by product type.
The tax amount on sales must be available in visualizations.
Sales managers are unable to create visualizations for sales grouped by Product type.
Sales managers must only be able to see sales for their territory.
Sales Schema

Territory Workbook

QUESTION
You need to address the naming issue.
Which two actions should you perform? Each correct answer presents part of the solution.

Select 2 option(s):

Replace the underscores with spaces in column names.

Replace the values in the data.

Rename column names containing abbreviations.

Unpivot the Product Name column

Q53. You create a Power Bl dataset from your organization’s data warehouse using DirectQuery with the tables shown in the below schema exhibit.

You create a calculated table named Country from the dataset for unique values of country in the table named Customer.
You generate visualizations for the sales data including showing sales by product manager. The product manager data is not in the data warehouse. The product manager data is in an Excel workbook on your local computer as shown in the Workbook exhibit below.

You do not have the Power BI On-Premises Gateway installed. You need to explain to the user when the data in the visualizations was last refreshed.
Visuals referring to the Country table show the values from..

Select 1 option(s):

when the user selected Refresh dashboard tiles

when the dataset was last refreshed

when the table was created

when the user pressed F5

the latest values in the data warehouse

Q54. You create a dataset using multiple data sources that contain similar tables. You need to reduce the number of tables in the dataset to make the data model simpler and the creation of visuals easier.

Which method should you use to combine the tables for the below scenario? Select the appropriate method.
Scenario: A folder containing JSON documents containing the same structure

Select 1 option(s):

Append Queries

Combine Files

Merge Queries

Q55. You import an HR dataset into Power Bl Desktop.

You need to see how many empty and error rows are in a dataset, as shown below.

Which two data quality options can you use to meet your goal? Each correct answer presents a complete solution.

Select 2 option(s):

Column distribution

Column profile

Custom column

Column quality

Q56. You create a Power Bl report using an Excel workbook stored on OneDrive for Business as the data source in Power Bl Desktop.

You need to replace the Excel workbook with a new version that contains updated data.
For the following statement(s), select Yes if the statement is true. Otherwise, select No.
“The Excel workbook must have the same structure as the original workbook.”

Select 1 option(s):

Yes

Q57. You have Employee and Salary tables in a data model. You need to include employee salaries in the Employee table, as shown in the below images.

Before transformation:

After transformation:

Which transformation should you use? Choose the correct answer

Select 1 option(s):

Append queries

Merge queries

Merge queries as new

Append queries as new

Q58. Your company moved from a self-service approach to an increasingly centralized approach. The IT team created a SQL Server Analysis Server (SSAS) for you to use.

When using live connections for SSAS, why might the Relationships view disappear in Power BI?

Select 1 option(s):

The Relationship view is hidden by default because changes are pushed back to the underlying SSAS data model.

The relationship view is only available for data from SQL Server.

The model's relationships are managed in SSAS, not in Power Bl Desktop.

Live connections automatically determine the relationships between tables.

Live connections do not support cross-table formulas.

Q59. Select the feature that allows you to navigate through HTML tags to get the data from a website?

Select 1 option(s):

Scaffolding

Get JSON Data

Web Scraping

Q60. “You plan to use dataflows to perform extract, transform, and load processing on data stored in the Common Data Service to prepare it for Power Bl reports.

You need to set up the dataflows to process and store data.
Which components should you use for the following process?
”
“Store dataflows using….”

Select 1 option(s):

Azure SQL Database

Power BI Datasets

Azure Data Lake Gen2

Common Data Service

Q61. You are a data analyst using Power Bl with Power Query to connect and get the data.

You use the Common Data Service as a data source.
You need to investigate query performance issues, but the View Native Query option is disabled.
What is a possible cause for the View Native Query option being disabled?

Select 1 option(s):

The Common Data Service does not support Query folding.

There is a conditional column transformation step.

There is a measure on the dataset.

The storage mode is set as Import.

Q62. You are a business intelligence analyst for a worldwide organization.

You create a Power Bl report that shows sales for your organization.
You need to provide a version of the report that filters the data for a single country.
Your Solution:
1) Add a report-level filter.
2) Share the report.
3) Check the Share report with the current filters and slicers option.
Does this solution meet the goal?

Select 1 option(s):

Yes

Q63. You are creating a dataset and modeling the data for a Power Bl report. The report should contain up-to-date information. The tables in the data model are shown in the exhibit and are described below:

Dual

DirectQuery

Import

Live Connection

Q64. Which view shows the graphical representation of data tables structure in query editor?

Select 1 option(s):

Query Dependency

Advanced Editor

Manage Data-source

Q65. CASE 3

You have the schema for the tables used in a bicycle franchise. In your role as a BI consultant, you have been asked to generate some insights from the data.
Company data schema:

You have been asked to add a column for profit, revenue price.
What steps do you need to do to calculate profit in the model?

Select 1 option(s):

Unpivot the Unit Price column -> Create custom column named Profit -> Set formula Sales Amount (Order Quantity * UnitPrice)

Create custom column named Profit -> Set formula Sales Amount (Order Quantity * UnitPrice) -> Set the data type to Fixed decimal

Duplicate the Order Quantity Column -> Create a new column and multiply duplicated column by UnitPrice-> Create custom column named Profit

Create a Group By on SalesOrderLineKey -> Sum by Sales Amount and rename Profit -> Subtract Unit Price