Create relationship without unique IDs

[melodypoh]

Hi, I am trying to compare sales from August 2017 (file 1) to August 2015 and 2016 (file 2). I can't connect them because there are duplicated SKUs sold all the time. How would you guys may go about this?

[Ashish_Mathur]

Hi,

So assuming you have 3 datasets each with 2 columns - Date and SKU's.  You must do the following:

1. Create a calendar table and build a relationship from the date column of each dataset to the calendar table
2. From all 3 datasets, create a master list of unique SKU's (can be done using Append and Remove duplicates feature of Power Query)
3. Build a relationship from the SKU column of each dataset to the unique list of SKU's dataset created in 2 above

Hope this helps.

[melodypoh]

Hi Ashish, How do you build a relationship using the date column? They are all different dates right? It's been a long time since I touched SQL. I feel like I am starting over again.

[Ashish_Mathur]

Hi,

Once you have a date column in each dataset, you have to go to Modelling > Manage relationship and create a relationship from the date column of each dataset to the date column of the calendar table.  Please read up on how to create a relationship in Power BI desktop/PowerPivot.

[melodypoh]

Hi Ashish, 

There are 2 datasets, one is for this year, the other one has the last 2 years' data. I don't think I should delete duplicated SKUs because each has its unique order number which differs year to year.

[KHorseman]

Connect them both to a shared calendar table that contains all dates for both years, then compare Augusts from there.

[melodypoh]

Hi there, thanks for your help. I am gonna have to look into how to create a shared calendar table.

[v-huizhn-msft]

Hi @melodypoh,

There must be a min and max date in the three datasets. Please create a calendar table using the formula.

Calendar=CALENDAR([MinDate], [MaxDate])

Best Regards,
Angelia

[melodypoh]

Hi Angela,

I am new to Power BI. Once I create a shared calendar table, how do I merge the two database together?

[v-huizhn-msft]

Hi @melodypoh,

You need to create a relationship between Calendar to file1(database1), another relationship between Calendar to file2(database2), how to create relationship, please review this article: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/.

Best Regards,

Angelia