PL-300 · Question #71
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer…
The correct answer is C. Add a second Date table named Ship_date to the model. Create a one-to-many relationship from. To enable analysis by both Order_date and Ship_date from an Order table using a common Date dimension, create a second role-playing Date table for the additional date attribute.
Question
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. You have a Microsoft SQL Server database that contains the following tables. The following columns contain date information:
- Date[Month] in the mmyyyy format
- Date[Date_ID] in the ddmmyyyy format
- Date[Date_name] in the mm/dd/yyyy format
- Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows. The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables. You plan to use Power BI Desktop to create an analytics solution for the data. You need to create a chart that displays a sum of Order[Order_amount] by month for the Order_ship_date column and the Order_date column. How should you model the data?
Exhibit
Options
- ACreate a one-to-many relationship from Date[Date_ID] to Order[Order_date] and another
- BAdd a second Date table named Ship_date to the model. Create a many-to-many relationship
- CAdd a second Date table named Ship_date to the model. Create a one-to-many relationship from
- DCreate a one-to-many relationship from Date[Date_ID] to Order[Order_date] and another
How the community answered
(20 responses)- A10% (2)
- B5% (1)
- C80% (16)
- D5% (1)
Why each option
To enable analysis by both Order_date and Ship_date from an Order table using a common Date dimension, create a second role-playing Date table for the additional date attribute.
Creating two relationships from the *same* Date table to the Order table is problematic because Power BI allows only one active relationship between two tables, limiting simultaneous analysis by both date types without complex DAX.
A many-to-many relationship from a Date table to a fact table's date key is generally an incorrect cardinality; date dimensions should typically have a one-to-many relationship to the fact table.
When analyzing a fact table with multiple date columns (like Order_date and Ship_date) against a single Date dimension, it's best practice to use separate, role-playing date dimension tables. Creating a second Date table (e.g., 'Ship_date') allows for two independent, active one-to-many relationships, enabling filtering and analysis by both date types simultaneously without conflicting active relationships.
Similar to A, using two relationships from the *same* Date table, even with an inactive one, requires explicit DAX functions like USERELATIONHIP for the inactive relation, which is less straightforward than having two active role-playing dimensions.
Concept tested: Role-playing dimensions and active/inactive relationships
Source: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships#multiple-relationships
Topics
Community Discussion
No community discussion yet for this question.