PL300-Quiz8

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date

and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.

What should you do?

You have a Microsoft Excel file in a Microsoft OneDrive folder.

The file must be imported to a Power BI dataset.

You need to ensure that the dataset can be refreshed in powerbi.com.

Which two connectors can you use to connect to the file? Each correct answer presents a complete solution.

NOTE: Each correct selection is worth one point.

62) You are creating a query to be used as a Country dimension in a star schema.

A snapshot of the source data is shown in the following table.

Country	City
USA	Seattle
USA	New York
USA	Denver
UK	Manchester
UK	London
Japan	Tokyo
Brazil	Rio
Brazil	Sao Paulo

You need to create the dimension. The dimension must contain a list of unique countries.

Which two actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

You use Power Query to import two tables named Order Header and Order Details from an Azure SQL database. The Order

Header table relates to the Order Details table by using a column named Order ID in each table.

You need to combine the tables into a single query that contains the unique columns of each table.

What should you select in Power Query Editor?

You are creating a report in Power BI Desktop.

You load a data extract that includes a free text field named coll.

You need to analyze the frequency distribution of the string lengths in col1. The solution must not affect the size of the

model.

What should you do?

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] = “Kentucky”

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?

You are modeling data by using Microsoft Power BI. Part of the data model is a large Microsoft SQL Server table

named Order that has more than 100 million records.

During the development process, you need to import a sample of the data from the Order table.

Solution: You write a DAX expression that uses the FILTER function.

Does this meet the goal?

You import a Power BI dataset that contains the following tables:

-Date

-Product

-Product Inventory

The Product Inventory table contains 25 million rows. A sample of the data is shown in the following table.

ProductKey	DateKey	MovementDate	UnitCost	UnitsIn	UnitsOut	UnitsBalance
167	20101228	28-Dec-10	0.19	0	0	875
167	20101229	29-Dec-10	0.19	0	0	875
167	20110119	19-Jan-11	0.19	0	0	875
167	20110121	21-Jan-11	0.19	0	0	875
167	20110122	22-Jan-11	0.19	0	0	875

The Product Inventory table relates to the Date table by using the DateKey column. The Product Inventory table relates to the Product table by using the ProductKey column.

You need to reduce the size of the data model without losing information.

What should you do?

Name	Used field	Filter
Supplier usage by count and value of invoices	Suppliers[ID] Suppliers[Name] LineItems[Invoice ID] LineItems[Price]	None
Spend by supplier location	Suppliers[Country] LineItems[Price]	None
Top 10 largest invoices last month	LineItems[Invoice ID] LineItems[Price]	LineItems[Invoice Date] in last calendar month

You need to minimize the size of the dataset without affecting the visuals.

What should you do?

You have the Power BI data model shown in the following exhibit.

The Sales table contains records of sales by day from the last five years up until today’s date. 

You plan to create a measure to return the total sales of March 2021 when March 2022 is selected.

Which DAX expression should you use? 

From Power Query Editor, you profile the data shown in the following exhibit.

The loT GUID and loT ID columns are unique to each row in the query.

You need to analyze loT events by the hour and day of the year. The solution must improve dataset performance.

Solution: You remove the loT GUID column and retain the loT ID column.

Does this meet the goal?

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date

and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.

What should you do?

You have a CSV file that contains user complaints. The file contains a column named Logged. Logged contains the date

and time each complaint occurred. The data in Logged is in the following format: 2018-12-31 at 08:59.

You need to be able to analyze the complaints by the logged date and use a built-in date hierarchy.

What should you do?

You have a Power BI data model that contains a table named Employees. The table has the following columns:

-Employee Name

-Email Address

-Start Date

-Job Title

You are implementing dynamic row-level security (RLS).

You need to create a table filter to meet the following requirements:

-Users must see only their own employee data.

-The DAX expression must work in both Power BI Desktop and the Power BI service.

Which expression should you use?

You have a Power BI model that contains a table named Sales. The Sales table contains the following columns:

-Order Line ID

-Product ID

-Unit Price

-Order ID

-Quantity

Orders are uniquely identified by using the order ID and can have multiple order lines. Each order line within an order contains a

different product ID.

You need to write a DAX measure that counts the number of orders.

Which formula should you use?

You have a Power BI model that contains a table named Date. The Date table contains the following columns:

-Date

-Fiscal Year

-Fiscal Quarter

-Month Name

-Calendar Year

-Week Number

-Month Number

-Calendar Quarter

You need to create a calculated table based on the Date table. The calculated table must contain only unique combinations of

values for Calendar Year, Calendar Quarter, and Calendar Month.

Which DAX function should you include in the table definition?

You have a report that contains a bar chart and a column chart. The bar chart shows customer count by customer

segment. The column chart shows sales by month.

You need to ensure that when a segment is selected in the bar chart, you see which portion of the total sales for the month

belongs to the customer segment.

How should the visual interactions be set on the column chart when the bar chart is selected?

You need to create a visualization that compares revenue and cost over time.

Which type of visualization should you use?

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?

What can you identify from the visual?

How should you create the dashed horizontal line denoting the 40th percentile of daily sales for the period shown?

You have a report that contains a donut chart and a clustered column chart. Interactions between the visuals use the

default settings.

You need to modify the report so that when you select a column in the column chart, the donut chart redraws by using the

data from the selected column.

What should you do?

You are creating a Power BI report by using Power BI Desktop.

You need to include a visual that shows trends and other useful information automatically. The visual must update based

on selections in other visuals.

Which type of visual should you use?

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as the

axis. Salary is present in the data as a numerical amount representing US dollars.

You need to create a reference line to show which employees are above the median salary.

Solution: You create a constant line and set the value to .5.

Does this meet the goal?

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee

as the axis. Salary is present in the data as a numerical amount representing US dollars.

You need to create a reference line to show which employees are above the median salary.

Solution: You create an average line by using the Salary measure.

Does this meet the goal?

You have a clustered bar chart that contains a measure named Salary as the value and a field named Employee as

the axis. Salary is present in the data as a numerical amount representing US dollars.

You need to create a reference line to show which employees are above the median salary.

Solution: You create a percentile line by using the Salary measure and set the percentile to 50%.

Does this meet the goal?

You have a Power BI report hosted on powerbi.com that displays expenses by department for department managers.

The report contains a line chart that shows expenses by month.

You need to enable users to choose between viewing the report as a line chart or a column chart. The solution must

minimize development and maintenance effort.

What should you do?

You have a line chart that shows the number of employees in a department over time.

You need to see the total salary costs of the employees when you hover over a data point.

What should you do?

You have a Power BI model that contains the following data.

Table name	Column name	Description	Data type
Date	Date	Calendar date	Date
Date	Month	Calendar month	Text
Date	Year	Calendar year	Integer
Sales	Sale	Sales value	Decimal number
Sales	Date	Calendar date	Date

The Date table relates to the Sales table by using the Date columns.

The model contains the following DAX measure.

Total Sales = SUM(Sales|[Sale])

You need to create another measure named Previous Quarter to display the sales one quarter before the selected

period.

Which DAX calculation should you use?

You have a Power BI report that contains a table visual The visual contains a column.

The column contains whole numbers ranging from o* 1 to 20.

You need to use conditional formatting to meet the following requirements:

* Visually compare the values without having to read the text containing the number.

* Show a different format for each distinct value

* Hide the numeric value of ColumnA.

* Minimize development effort.

Which formatting should you use?

You have a data source that contain a column. The column contains case sensitive data.

You have a Power BI semantic model in DirectQuery mode.

You connect to the model and discover that it contains undefined values and errors.

You need to resolve the issue.

Solution: You implicitly convert the values into the required types.

Does this meet the goal?

You have a Power B1 dataset named Finance that is hosted in a Power B1 workspace.

The finance team at your company is NOT currently a member of any Power B1 workspace roles.

You need to enable the finance team to use Microsoft Excel to analyze the Finance dataset.

What should you do?

You have a Power B1 dataset that contains quarterly sales performance data.

You need to enable managers to review the data in a format that meets the following requirements:

* Is optimized for printing.

* Renders data in Microsoft Excel, Word, PowerPoint, and PDF formats.

What should you create?

You have a source that contains a comumn. The column contains case sensitive date. You have a Power BI

semantic model in DirectQuery mode. You connect to the model and discover that it contains undefined values

and errors. You need to resolve the issue.

Solution: You add an index key and normalize casing in the data source.

Does this meet the goal?

You have a Power BI data model that imports a date table and a sales table from an Azure SQL database data source.

The sales table has the following date foreign keys.

* Due Date

* Order Date

* Delivery Date

You need to support the analysis of sales over time based on all three dates at the same time.

Solution: From the Data pane, you rename the date table as Due Date. You use a DAX expression to create Order Date and

Deliver Date as calculated tables. You create active relationships between the sales table and each date table.

Does this meet the goal?