Supporting Analysis with Multiple Date Foreign Keys in Power BI

Design and implement a data model

Answer Correct 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.

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?

Yes
No Correct Answer

Community Insight

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.

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.

Community Discussion (64 comments)

MissElla 👍 1 Selected: B

NO Referencing is not a good option

12cad83 👍 3 Selected: 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.

Dusica 👍 1

that is called role playing dimensions

Lulu_2022 👍 2 Selected: A

By referencing the original Date query in Power Query (not duplicating it), you create three separate queries — one for each role-playing dimension: Due Date Order Date Delivery Date Each of these can then be related to the corresponding foreign key in the Sales table. This allows you to: Maintain active relationships for all three date fields Avoid using USERELATIONSHIP() in DAX Enable time-based analysis across all three date types in visuals This is a common and effective approach when dealing with multiple date roles in a fact table. 🔧 Bonus tip: Make sure to rename each referenced query clearly (e.g., Date - Order, Date - Delivery) so your model stays clean and intuitive. Let me know if you’d like help writing measures that compare these timelines side by side — it’s a great way to uncover delivery delays or order processing trends 📦📅📊

Emiliio 👍 1

The solution doesnt mention anything about relationships. The answer is incomplete.

b2775a4 👍 1 Selected: A

• Power BI only allows one active relationship between two tables. By creating separate date tables, you can create active relationships for all three date keys. • Each date table filters the sales table independently, allowing accurate analysis based on • Referencing the date table avoids duplicating the table manually and keeps the solution efficient.

jaume 👍 2 Selected: B

I think even referencing two queries to the first one is not ensuring the import of just a sample of the data, this only can be achieved by filtering the data in the query retrieving the data into our model. If first query is not set in the right way to get only sample of the data, other two queries would retrieve same dataset and it would be not just a sample. My vote here is for B.No

jaume 👍 1

Dismiss my previous comments, I though this is still series initiated in Q29 but Q29 is a single question despite what it mentions at the beginning. Spliting the date table in three tables would allow us to set three ACTIVE relationships in the data model to support the analysis but there is nothing related to the relationships mentioned in the question so I would vote for B.No

8b7b763 👍 1

A. Yes. You can only have 1 active relationship at a time. Userelationship places processing on the report layer via dax. Use powerquery to add the dimension date tables. Do your regular Dax calculations after.

rcaliandro 👍 1 Selected: B

I would say No

agelee 👍 1

I think B. Nothing said about USERELATIONSHIP. Without mentioning it I think the answer is NO.

bc23451 👍 1

I think it's B because Due Date come after Order Date so if we reference with the Due Date, you may not have all date for Order Date. That's my point of view.

jsav1 👍 1 Selected: A

It gives you three separate date tables but without having to import the date data 3 times.

GowthamMupparapu 👍 2 Selected: A

Please suggest if my thought process is not aligned with the question's constraints. As it is said in the proposed solution cant we reference the initial date table 2 more times to obtain other date tables ?? (By renaming the tables and deleting other two irrelevant columns)

Dsbuff 👍 3 Selected: 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.

burto 👍 2

wouldnt it be that since they are imported datasets it would be more productive to use DAX to transform the data?

WRTopics 👍 2 Selected: B

I'll select B.

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).

rgabage 👍 4 Selected: A

A. This was in the learning path.

Elektrolite 👍 12

link, or gtfo

Badadadiiii 👍 3 Selected: 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.

Acrious 👍 3 Selected: B

I am going with B and here is why. It says they renamed it. Then they said they used it for 2 of the dates, not all 3. So technically there isn't 3 queries being run just 2... Thoughts?

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.

miro26 👍 1

In such a scenario, I wonder if it makes sense to import a time dimension at all. Better to create three dimensions based on calculated tables like here.

ApacheKafka 👍 2

In the documentation, It is advised that. You always use the provided date table if available. Only in situations where they dont exist should you create a calculated date table.

Maniula 👍 7 Selected: A

A. Again, this was in the learning path, duplicate date tables with DAX or PowerQuery.

SamuComqi 👍 16 Selected: A

umairtaqi 👍 2

Please confirm the answer.

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.

xkapelis 👍 6 Selected: A

yes it will work

Rinesh25 👍 3 Selected: B

The Answer is correct

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

cs3122 👍 4

100% agree. There is so much ambiguity in the questions, and can easily be interpreted ways.

MoxieTT 👍 10

I keep reading "trick" questions when reading peoples' experiences recently of PL-300.

UlyUkr 👍 2

I would say No, because the solution does not specify changing the relationships between referenced tables to use the correct foreign keys.

semauni 👍 2

No. We're dealing with role-playing dimensions here. Creating multiple date tables with separate relationships give confusing results, as Guy in A Cube explains very well in his video.

reignreign03 👍 1

Bro, what Guy in A Cube explained is although there can be multiple relationships between Sales and Date tables, you can add a DAX function using USERELATIONSHIP to make it work.

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 ?

RazaTheLegend 👍 5 Selected: 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.

Nemesizz 👍 4 Selected: 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

Andrew9834523 👍 3 Selected: A

A is correct to my mind. From my experience cloning date tables is not a bad idea and MS guides agree with me (see: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive)

Aneran 👍 3 Selected: 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.

Maniula 👍 1

Power BI automatically detects most relationships.

killershark 👍 4 Selected: 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.

quxxy 👍 1

The answer is "B": the cause is that dates in Order date column and Delivery date column could be different with Due date column, so the queries could contain lesser numbers of rows witch means better performance...

SanaCanada 👍 1

Answer is correct B no confusion

Michcat 👍 1

Creating two additional tables in Power Query can be a possible solution: Remove any inactive relationships.

Neilsy 👍 3 Selected: 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

nmosq 👍 6 Selected: A

It's not going to be great solution from the performance side... but that's not part of the requirements

GuerreiroJunior 👍 3

The answer is no, we must have one active relationship, and the others (two) must be innative an then for each innative reationship we can use the USERELATIONSHIP DAX function

MBA_1990 👍 5 Selected: A

Answer is YES. That's not the best solution regarding the performance but it's not the subject.

Dr_Do 👍 11 Selected: B

Answer is NO. Renaming of the query doesn't make sense.

Anne2909 👍 2 Selected: B

B. No, is the correct answer.

lcamp 👍 4 Selected: A

based on the scenario, you are ABLE to SUPPORT.

lukelin08 👍 3 Selected: B

B. No is correct answer given

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

Booster21 👍 3 Selected: B

The given answer is correct.

fdsdfgxcvbdsfhshfg 👍 1

They didn't ask for best performing solution mister

lukelin08 👍 2

Again Neville comes to the rescue and makes some sense in here

fdsdfgxcvbdsfhshfg 👍 4 Selected: 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.

Mirjalol 👍 4

Stop misleading everyone.

NevilleV 👍 6

No way! If that works, its way too complicated and a burden on performance. the answer is No

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).

Comments & Corrections

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

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 →

Related Analysis

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