PL-300: Microsoft Power BI Data Analyst
You import two Microsoft Excel tables named Customer and Address into Power Query. Customer contains the following columns:
- Customer ID
- Customer Name
- Phone
- Email Address
- Address ID
Address contains the following columns:
- Address ID
- Address Line 1
- Address Line 2
- City
- State/Region
- Country
- Postal Code
The Customer ID and Address ID columns represent unique rows.
You need to create a query that has one row per customer. Each row must contain City, State/Region, and Country for each customer.
What should you do?
Merge the Customer and Address tables.
Transpose the Customer and Address tables.
Group the Customer and Address tables by the Address ID column.
Append the Customer and Address tables.
Answer is Merge the Customer and Address tables.
There are two primary ways of combining queries: merging and appending.
- When you have one or more columns that you would like to add to another query, you merge the queries.
- When you have additional rows of data that you would like to add to an existing query, you append the query.
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data
You have a data model that contains many complex DAX expressions. The expressions contain frequent references to the RELATED and RELATEDTABLE functions.
You need to recommend a solution to minimize the use of the RELATED and RELATEDTABLE functions.
What should you recommend?
Split the model into multiple models.
Hide unused columns in the model.
Merge tables by using Power Query.
Transpose.
Answer is Merge tables by using Power Query.
Combining data means connecting to two or more data sources, shaping them as needed, then consolidating them into a useful query.
When you have one or more columns that you'd like to add to another query, you merge the queries.
Note: The RELATEDTABLE function is a shortcut for CALCULATETABLE function with no logical expression.
CALCULATETABLE evaluates a table expression in a modified filter context and returns A table of values.
Reference:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-shape-and-combine-data
You have a Power BI report. You need to create a calculated table to return the 100 highest spending customers. How should you complete the DAX expression?
Box 1: TOPN
TOPN returns the top N rows of the specified table.
Box 2: SUMMARIZE
SUMMARIZE returns a summary table for the requested totals over a set of groups.
Box 3: DESC
Sort in descending order.
It is last in the TOPN command.
TOPN syntax:
TOPN(n_value, table, orderBy_expression, [order[, orderBy_expression, [order]]])
Reference:
https://docs.microsoft.com/en-us/dax/topn-function-dax
https://docs.microsoft.com/en-us/dax/summarize-function-dax
Your company has affiliates who help the company acquire customers.
You build a report for the affiliate managers at the company to assist them in understanding affiliate performance.
The managers request a visual showing the total sales value of the latest 50 transactions for each affiliate. You have a data model that contains the following tables.
The Affiliate table has a one-to-many relationship to the Transactions table based on the AffiliateID column.
You need to develop a measure to support the visual.
How should you complete the DAX expression?
Box 1: CALCULATE
Start with CALCULATE and use a SUMX.
CALCULATE evaluates an expression in a modified filter context.
Box 2: SUM
Box 3: TOPN
TOPN returns the top N rows of the specified table.
TOPN Syntax: TOPN(n_value, table, orderBy_expression, [order[, orderBy_expression, [order]]])
Box 4: [TransactionID]
The orderBy_expression: Any DAX expression where the result value is used to sort the table and it is evaluated for each row of table.
Reference:
https://docs.microsoft.com/en-us/dax/topn-function-dax
You need to create a component measure that can be used as an input for a Percent of all returns calculation. The component measure needs to always show total returns, regardless of external filter context. Which of the following measures should you create?
CALCULATE([Total Orders], ALL(Returns))
SUMX('Returns', 'Returns'[quantity returned])
CALCULATE([Total Returns], ALL(Returns))
SUM(Returns[quantity returned])
Answer is CALCULATE([Total Returns], ALL(Returns))
You start building out a dynamic calendar table in the query editor. Which of the following statements would you use to create a Start of Week column that begins on Monday?
Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([Date],Day.Sunday), type date)
Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([Date],Day.Monday), type date)
Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.EndOfWeek([Date],Day.Sunday), type date)
Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.EndOfWeek([Date],Day.Tuesday), type date)
Answer is Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([Date],Day.Monday), type date)
What does DAX stand for, in the context of Power BI?
Data Analysis Expressions
Data & Analysis Exchange
Data Aggregation Expressions
Dogs Against Xylophones
Answer is Data Analysis Expressions
Data Analysis Expressions, known as DAX, is the formula language that drives Power BI.
Where should you create calculated columns to reduce model size and improve performance?
Source data
Power Query
Data model
They all achieve the same results
Answer is Source data
Creating calculated columns as close to the source as possible helps reduce data model size and improve performance.
Which of the following is NOT true about measures?
They are evaluated based on row context
They reference entire tables or columns
They aren't visible within tables
They recalculate in response to any change to filters within the report
Answer is They are evaluated based on row context
In what field do measures typically "live" in a visual?
Rows
Columns
Values
Filters
Answer is Values
Measures are numerical, calculated values that can be analyzed in the “values” field of a report visual.