cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anileshknpowerb
Helper III
Helper III

Reference vs Duplicate vs Calculated Table

Hello Experts,

I have a table SalesFact and I need to join 2 Dates, i,e, ShipDate and OrderDate.

 

What is the better approach to handle this? And why is the approach is better?

 

1. Duplicate the Date Dimension in power Query Editor for OrderDate..

2. Create reference of Date Dimension in Power Query Editor for OrderDate.

3. In the modeling Tab, Use the "New table" option like below

    NewTable = DimOrderDate

 

Thanks,

Anilesh

4 REPLIES 4
v-yingjl
Community Support
Community Support

Hi @anileshknpowerb ,

  1. Duplicate a table means copy the same previous table but if you have some new actions about the previous fact table, it will not be affected, it has benn an independent table.
  2. Reference means the table will be affected by the previous fact table. When a query references a second query, it's as though the steps in the second query are combined with, and run before, the steps in the first query. You can refer this document: Referencing Power Query queries . 
  3. Calculated tables let you add new tables based on data you've already loaded into the model. Instead of querying and loading values into your new table's columns from a data source, you create a Data Analysis Expressions (DAX) formula to define the table's values. Please refer this document: Create calculated tables in Power BI Desktop 

In conclusion, Duplicate is generally used when you would like to create a similar query and you do not want to type the same code. Reference in Query means you would like to use that Query results in some other queries where your original query remains as a base Query and it can be used in other queries for the further processing. 

 

In addition, this viedo and article explain the difference between Duplicate and Reference in details that you can refer: 

  1. Difference between duplicate and reference a query in Power Query 
  2. Reference vs Duplicate in Power BI 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

vanessafvg
Super User I
Super User I

use the same date dimension for both dates but one will be inactive and other active, and then you need to use the userelationship() function 

 

https://www.youtube.com/watch?v=sONvctPlplY   this video explains well

 

so just as you would use a role playing dimension in tradition kimball star schema (dont know if that makes sense), you can do the same in power bi, you just need to utilise the above function.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I want to show OderDate and ShipDate as separate dates so that users can do adhoc analysis on both dates. In that case, which one I should go with?

Hi @anileshknpowerb ,

 

If you just want to show it as separate dates, you can use Duplicate to try. To do some analysis, you may use some relationships like @ vanessafvg mentioned previously.

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors