PL-300: Microsoft Power BI Data Analyst
How many inactive relationships are there in the model shown above?
1
2
3
None
Answer is 1
The dotted line connecting the Calendar and Sales tables is an inactive one-to-many relationship. The other two relationships are active.
Which of these functions can be used to activate inactive relationships?
RELATED
RELATEDTABLE
USERELATIONSHIP
ACTIVATE
Answer is USERELATIONSHIP
The USERELATIONSHIP function allows you to determine which relationship to use between to tables, including inactive relationships.
If you import or create your own date table, which of these requirements must it meet?
Must contain all the days for all years represented in the model
Cannot contain duplicate dates
Must have at least one field set as a Date or DateTime datatype
All of the above
Answer is All of the above
To import or create your own date table, it must meet all 3 of these requirements, and all times must be identical (i.e. 12:00).
You are creating a report in Power BI Desktop.
You are consuming the following tables.
You have a new table named Fiscal that has the same schema as the Date table, but contains the fiscal dates of your company.
You need to create a report that displays the total sales by fiscal month and calendar month.
What should you do?
Union Fiscal and Date as one table.
Add Fiscal to the model and create a one-to-many relationship by using Date[Year] and Fiscal[Year].
Add Fiscal to the model and create a one-to-one relationship by using Date[Year] and Fiscal[Year].
Merge Fiscal into the Date table.
Answer is Merge Fiscal into the Date table.
There are two primary ways of combining queries: merging and appending:
- When you have one or more columns that you’d like to add to another query, you merge the queries.
- When you have additional rows of data that you’d like to add to an existing query, you append the query.
References:
https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data
You have a Microsoft Excel 2016 workbook that has a Power Pivot model. The model contains the following tables:
- Product (Product_id, Product_Name)
- Sales (Order_id, Order_Date, Product_id, Salesperson_id, Sales_Amount)
- Salesperson (Salesperson_id, Salesperson_name, address)
The model has the following relationships:
- Sales to Product
- Sales to Salesperson
You create a new Power BI file and import the Power Pivot model.
You need to ensure that you can generate a report that displays the count of products sold by each salesperson.
What should you do before you create the report?
Create a one-to-one relationship between Product and Salesperson.
For each relationship, change the Cross filter direction to Both.
For each relationship, change the Cardinality to One to one (1:1).
Change a many-to-one relationship between Product and Salesperson.
Answer is For each relationship, change the Cross filter direction to Both.
Their's no need to create another relationship, the model already has one that extends to the needed level. You know only need to allow the Sales table to filter Product an you can do that by changing the cross filter to both.
References:
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
You have two tables named CustomerVisits and Date in a Power BI model.
You create a measure to calculate the number of customer visits. You use the measure in the report shown in the exhibit.
You discover that the total number of customer visits was 60,000, and that there were only 5,000 customer visits in August.
You need to fix the report to display the correct data for each month.
What should you do?
Modify the measure to use the CALCULATE DAX function.
Create a relationship between the CustomerVisits table and the Date table.
Modify the measure to use the sum DAX function.
Create a hierarchy in the Date table.
Answer is Create a relationship between the CustomerVisits table and the Date table.
References:
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-measures
You have the following tables.
There is a many-to-one relationship from Subscriber to Date that uses Subscriber[StartDate] and Date[Date]. The Cross filter direction of the relationship is set to Single.
You plan to create a column chart that displays the following two measures:
- Count of SubscriberID by Month based on the StartDate
- Count of SubscriberID by Month based on the EndDate
What should you do before you create the measures?
Create an active one-to-one relationship from Subscriber[StartDate] to Date[Date].
Change the Cross filter direction of the active relationship to Both.
Change the active relationship for many-to-one.
Create an inactive many-to-one relationship from Subscriber[StartDate] to Date[Date].
Answer is Create an inactive many-to-one relationship from Subscriber[StartDate] to Date[Date].
There already is relationship between Subscriber[StartDate] and Date[Date]" but option D asks you to create INACTIVE relationship. To do that you first have to create a different ACTIVE relationship between Subscriber[EndDate] and Date[Date].
References:
https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi
You have a query that retrieves sales data. A sample of the data is shown in the following table.
You need to ensure that the values in the Date column contain a date. Null values must be replaced with the date from the previous row.
What should you click on the Transform tab in Query Editor?
Format, and then Clean
Date, and then Earliest
Fill, and then Down
Replace Values, and then Replace Errors
Answer is Fill, and then Down
Under Fill in the Transform ribbon, you DOWN (for replacing null values with preceding values, and UP (replaces null values with values after them).
References:
https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/
You have a table named Sales. A sample of the data in Sales is shown in the following table.
You create a stacked column chart visualization that displays ProductName by Date.
You discover that the axis for the visualization displays all the individual dates.
You need to ensure that the visualization displays ProductName by year and that you can drill down to see ProductName by week and day.
What should you do first?
Create a new table that has columns for the date, year, week, and day.
Create a new hierarchy in the Sales table.
Format the visualization and set the type of the X-Axis to Categorical.
Configure a visual filter for the Date column that uses an advanced filter.
Answer is Create a new table that has columns for the date, year, week, and day.
1. create a Date table, including columns: date, week, month, quarter, year.
2. and then create relationship between table "date" and fact table "Sales via [Date].
3. hierarchy: Y/M/W/D
You have a Power BI data model that contains a Sale Date table.
You need to add a second date table named Ship Date that contains the same columns as those in Sale Date. The solution must NOT repeat the query logic.
What should you do to create the query for Ship Date?
Reference the Sale Date query.
Duplicate the Sale Date query.
Rename the Sale Date query to Ship Date.
Append the Sale Date query as a new query.