- (Exam Topic 4)
You have sales data in a star schema that contains four tables named Sales, Customer, Date, and Product. The Sales table contains purchase and ship dates.
Most often, you will use the purchase date to analyze the data, but you will analyze the data by both dates independently and together.
You need to design an imported dataset to support the analysis. The solution must minimize the model size and the number of queries against the data source.
Which data modeling design should you use?
Correct Answer:D
Microsoft recommends defining active relationships whenever possible. They widen the scope and potential of how your model can be used by report authors, and users working with Q&A.
Refactoring methodology (example): Here's a methodology to refactor a model from a single role-playing dimension-type table, to a design with one table per role.
Remove any inactive relationships. Consider renaming the role-playing dimension-type table to better describe its role. In the example, the Airport table is related to the ArrivalAirport column of the Flight table, so it's renamed as Arrival Airport. Create a copy of the role-playing table, providing it with a name that reflects its role. If it's an Import table, we recommend defining a calculated table. If it's a DirectQuery table, you can duplicate the Power Query query.
Only one relationship can be active.
Note: If you query two or more tables at the same time, when the data is loaded, Power BI Desktop attempts to find and create relationships for you. The relationship options Cardinality, Cross filter direction, and Make this relationship active are automatically set.
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships https://docs.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive
- (Exam Topic 4)
You have two tables named Customers and Invoice in a Power BI model. The Customers table contains the following fields:
CustomerID Customer City Customer State Customer Name Customer Address 1 Customer Address 2 Customer Postal Code
The Invoice table contains the following fields: Order ID Invoice ID Invoice Date Customer ID Total Amount Total Item Count
The Customers table is related to the Invoice table through the Customer ID columns. A customer can have many invoices within one month.
The Power BI model must provide the following information: The number of customers invoiced in each state last month The average invoice amount per customer in each postal code
You need to define the relationship from the Customers table to the Invoice table. The solution must optimize query performance.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Solution:
Box 1: One-to-many
A customer can have many invoices within one month. Box 2: Single
For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional). For
Single cross filter direction means "single direction", and Both means "both directions". A relationship that filters in both directions is commonly described as bi-directional.
Reference:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand
Does this meet the goal?
Correct Answer:A
- (Exam Topic 4)
You are reviewing a query that produces 10,000 rows in the Power Query Editor. You need to identify whether a column contains only unique values.
Which two Data Preview options can you use? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
Correct Answer:AB
B: Column distribution: This feature provides a set of visuals underneath the names of the columns that showcase the frequency and distribution of the values in each of the columns. The data in these visualizations is sorted in descending order from the value with the highest frequency.
By hovering over the distribution data in any of the columns, you get information about the overall data in the column (with distinct count and unique values).
A: Column profile: This feature provides a more in-depth look at the data in a column [compared to column distribution]. Apart from the column distribution chart, it contains a column statistics chart.
Reference:
https://docs.microsoft.com/en-us/power-query/data-profiling-tools
- (Exam Topic 4)
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this scenario, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have several reports and dashboards in a workspace.
You need to grant all organizational users read access to a dashboard and several reports. Solution: You enable included in app for all assets.
Does this meet the goal?
Correct Answer:B
- (Exam Topic 1)
You need to address the data concerns before creating the data model. What should you do in Power Query Editor?
Correct Answer:C