Supporting Analysis with Multiple Date Foreign Keys in Power BI

PL-300 By islever Editorial Last updated

Design and implement a data model

Short answer

B Renaming and referencing queries does not create the required relationships between the date table and the sales table's multiple date foreign keys.

Question

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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Power BI report 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 the date foreign keys.
Solution: From Power Query Editor, you rename the date query as Due Date. You reference the Due Date query twice to make the queries for Order Date and
Delivery Date.
Does this meet the goal?
  1. Yes
  2. No Correct Answer

Key Takeaways

The scenario tests the understanding of Active vs. Inactive relationships in DAX. The common trap is confusing data preparation (Power Query) with relationship modeling (Data Model), assuming renaming creates connectivity.

This question addresses how to handle multiple date foreign keys (Due, Order, Delivery) in a Power BI data model. It establishes that simply renaming and referencing queries in Power Query is insufficient for creating the necessary relationships.

Common mistake: Many learners choose 'Yes' because they believe duplicating the Date table via references in Power Query automatically links it to different columns in the Sales table. This is incorrect; physical tables do not imply logical relationships without defining them in the model.

Expert Analysis

Why the Answer Is Correct

The correct answer is B (No). Renaming a query and using "Reference" in Power Query Editor creates separate physical copies of the Date table in the data model. However, it does not automatically create relationships between these new Date tables and the specific date columns (Due Date, Order Date, Delivery Date) in the Sales table. To support analysis over all three dates, you must explicitly define inactive relationships (or use USERELATIONSHIP in DAX measures) between the single Date table and each of the three foreign key columns. Simply having duplicate tables doesn't solve the ambiguity or enable time intelligence across different date types.

Why the Other Options Are Wrong

Option A is incorrect because the proposed solution fails to establish the required cardinality and relationship type. Without explicit relationship definitions, the model cannot filter sales by Due Date or Delivery Date based on a standard Date dimension. The "Reference" operation only duplicates data structure, not semantic connections.

Community Comment Notes

Community consensus is split but leans towards B being the technically correct answer for exam purposes. User legionairemax notes that typically one active and two inactive relationships are created from one Date table. User Igetmyrole emphasizes that renaming doesn't create relationships. Some users like SamuComqi argue A is correct based on learning path experiences, but this often reflects a misunderstanding of the distinction between Power Query steps and Data Model relationships. As Dr_Do noted, "Renaming of the query doesn't make sense" for achieving the goal of supporting analysis based on specific keys.

Exam Strategy

When dealing with multiple date columns, always think about Relationship Cardinality. You need one Date table linked to multiple date columns via Inactive Relationships, not multiple duplicated Date tables. Use USERELATIONSHIP() in your DAX measures to activate these relationships for specific calculations.

Frequently Asked Questions

Why can't I just duplicate the Date table in Power Query?

Duplicating tables creates separate entities. You still need to manually define relationships between each duplicate and the specific date column in the fact table.

What is the best practice for multiple date columns?

Use a single Date table and create inactive relationships to each date column in the fact table. Activate them dynamically using USERELATIONSHIP in DAX.

More PL-300 FAQ →

Discussion 64 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 30 of 64

  1. junaid2107 👍 137
    I dont understand What micorsoft hopes to achieve with trick questions. If questions leave so much to the analysts assumptions then the questions isnt testing the candidates knowledge
  2. legionairemax 👍 23
    The answer is correct. However, I believe the alternate solution given is wrong. I would say that 3 relationships for each date respectively would be made from the Date table to the Sales table. One being active and the other two inactive. Thus, allowing to filter by a specific date column The solution suggested is not required, as the report does not require any output that involves all 3 dates. It requires only sales over one date column at a time. Moreover, being date tables, they can significantly increase the size of the model (considering that the table is sales related).
  3. SamuComqi 👍 16 Answered A
    A
  4. Elektrolite 👍 12
    link, or gtfo
  5. Dr_Do 👍 11 Answered B
    Answer is NO. Renaming of the query doesn't make sense.
Show 25 more comments
  1. MoxieTT 👍 10
    I keep reading "trick" questions when reading peoples' experiences recently of PL-300.
  2. Igetmyrole 👍 7
    B is the correct answer. No, this solution does not meet the goal of supporting the analysis of sales over time based on all the date foreign keys. Renaming the date query as "Due Date" and referencing it twice in Power Query Editor does not create relationships between the sales table and the date table based on the difference date foreign keys (Due Date, Order Date, and Delivery Date).
  3. og44 👍 7
    I gues whwn you reference twice, it means you are adding two aditional copies, hence you have 3 tables. But as the question does not talk about relationship, that is the source of ambiguity.
  4. Maniula 👍 7 Answered A
    A. Again, this was in the learning path, duplicate date tables with DAX or PowerQuery.
  5. shmmini 👍 6
    The answer is only part of the solution. It does not say about creating a relationship with each Date table. This is a true problem with some questions, are We supposed to assume that the relationship part is implicit ?
  6. nmosq 👍 6 Answered A
    It's not going to be great solution from the performance side... but that's not part of the requirements
  7. NevilleV 👍 6
    No way! If that works, its way too complicated and a burden on performance. the answer is No
  8. xkapelis 👍 6 Answered A
    yes it will work
  9. RazaTheLegend 👍 5 Answered B
    No, this solution does not meet the goal. While it is possible to use a single date table to support analysis based on multiple foreign keys, renaming the date query as one of the foreign keys and then referencing it twice does not accomplish this. Instead, the date query should be duplicated and renamed appropriately for each foreign key. Then, a relationship can be established between the sales table and each date table based on the appropriate foreign key.
  10. prikha16 👍 5
    I would say Yes, It does meet the goal though it may not be the ideal solution. The question doesn't state anything about reducing model size etc.. it just says it should support the required analysis. There is no confusion here, it is a case of role- playing dimensions and the solution would work.
  11. MBA_1990 👍 5 Answered A
    Answer is YES. That's not the best solution regarding the performance but it's not the subject.
  12. killershark 👍 4 Answered B
    Chat GPT says - No, the solution does not meet the goal of supporting the analysis of sales over time based on all the date foreign keys. Renaming the date query as "Due Date" and referencing it twice does not create separate queries for "Order Date" and "Delivery Date." Instead, it creates two identical queries that refer to the same "Due Date" query, which is not helpful for analyzing sales data based on all date foreign keys. To support the analysis of sales over time based on all the date foreign keys, you should create separate queries for "Order Date" and "Delivery Date" in Power Query Editor and then merge these queries with the sales table on their respective foreign keys. This will enable you to analyze sales data based on all date foreign keys and create meaningful insights.
  13. fdsdfgxcvbdsfhshfg 👍 4 Answered A
    Creating two additional tables in Power Query can be a possible solution: 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. In the example, the Departure Airport table was created by using the following calculated table definition.
  14. Nemesizz 👍 4 Answered A
    Answer is Yes. From a modelling perspective this is called a role-playing dimension. By cloning the three dimensions , we can connect each table to the sales fact table, in order to analyze by the different type of dates. Please read: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
  15. cs3122 👍 4
    100% agree. There is so much ambiguity in the questions, and can easily be interpreted ways.
  16. lcamp 👍 4 Answered A
    based on the scenario, you are ABLE to SUPPORT.
  17. rgabage 👍 4 Answered A
    A. This was in the learning path.
  18. Mirjalol 👍 4
    Stop misleading everyone.
  19. Aneran 👍 3 Answered B
    No, this solution does not meet the goal of supporting the analysis of sales over time based on all the date foreign keys. Renaming the date query as Due Date and referencing it twice to make the queries for Order Date and Delivery Date in Power Query Editor will only create duplicate queries, but it will not provide the necessary relationship between the sales table and the date table to support analysis of sales over time based on all the date foreign keys. To support this analysis, you would need to create separate relationships between the sales table and the date table for each date foreign key. This can be done by creating multiple copies of the date table, one for each date foreign key in the sales table, and then creating a relationship between each copy of the date table and the corresponding date foreign key in the sales table.
  20. Dsbuff 👍 3 Answered A
    A is correct. ChatGPT's response: Creating separate queries in Power Query for each date (Due Date, Order Date, and Delivery Date) by referencing a base date query is a recommended way to deal with role-playing dimensions. This method involves creating separate date tables for each date role you need to analyze. In Power BI, each of these tables will be related to the sales table using the respective date foreign key. This approach allows for creating active relationships between the sales table and each of the date tables, which enables the use of these dates in filters, slicers, and visuals without the need for DAX measures to handle inactive relationships. It is a common and efficient way to support analysis by different date roles within the same model.
  21. Hoeishetmogelijk 👍 3
    I think it is a little ambiguous. You should follow this refactory methodology: 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. This question is about an IMPORT table. So the use of a calculated table is recommended. That said, I think that duplicating the Power Query query also works for an IMPORT table. Using a calculated table for IMPORT is just a recommendation. Source: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive
  22. Aneran 👍 3 Answered B
    No, this solution does not meet the goal of supporting the analysis of sales over time based on all the date foreign keys. Renaming the date query as Due Date and referencing it twice to make the queries for Order Date and Delivery Date in Power Query Editor will only create duplicate queries, but it will not provide the necessary relationship between the sales table and the date table to support analysis of sales over time based on all the date foreign key
  23. Badadadiiii 👍 3 Answered A
    The key to answering this question correct, is this sentence "You need to support the analysis of sales over time based on all the date foreign keys." You cannot achieve this without 3 date tables, which is created in Power Query. 1 is loaded from Azure, the other two are created by reference to the first. If you have one date table with 1 active and 2 inactive relationships, then you won't be able to use all 3 dates in the same analysis.
  24. 12cad83 👍 3 Answered B
    No. Here’s why: Best practice in Power BI is to use a single Date dimension table, marked as a Date Table. You then create multiple relationships between Sales and the Date table (one for each date column). Only one relationship can be active at a time, but you can switch between them in DAX using USERELATIONSHIP(). 👉 Creating 3 different date tables is not needed and complicates the model.
  25. Neilsy 👍 3 Answered A
    The solution creates 3 date tables, one per date field hence active relationships with their corresponding date table can be created for each date. Te alternative is to create an additional two relationships on the single date table and use the USERELATIONSHIP function to activate them in the relevant measure

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

PREVIOUSYEAR returns the full previous year, not the same day of last year

S-grade

Shrinking a Power BI model for monthly transaction totals with a star schema

S-grade

Northwind Traders semantic model: choosing Import storage mode for the Power BI tables

S-grade

Connecting Power BI Desktop to Azure DevOps Analytics views with the OData Feed connector

S-grade

Practice All PL-300 Questions

Access 116 questions with complete answers and detailed explanations.

View Full PL-300 Practice Test →

← Back to PL-300 Study Guide