Not sure if this is the right section of the forum but here I go.
See attached PBIX -
3 tables
MasterSKU table are the items I’m trying to analyze.
90 day & 365 day sales tables are transactional items that I’m trying to bring together.
Unfortunately the core DB that generates this data doesn’t have a way to compare 90 & 365 sales thus the attempt at PowerBI as the Excel Vlookup is unruly.
I can link the MasterSku to each transactional table but when I try to bring the 3 tables together I get an error that PowerBI can’t determine the relationship between 2 tables.
I’m sure there is something on how I’m linking the information that’s resulting in this issue but for the life I’ve me I’m not seeing it.
PowerBI defaults to a ‘both’ way link which I manually changed to a One to Many link but even changing back doesn’t seem to resolve the issue.
Any ideas?
Thanks in advance.
Replenishment Test.pbix (6.6 MB)
Hi @gkavesh,
Your inquiry isn’t clear to me but I’ll give it a go anyway…
You failed to include a source file so I had to export the data in your model - to see its behaviour - only change I made was renaming all Key Columns to: SKU
I left the auto detect relationships enabled and PBI properly identified them and created a 1:1 relationship with the crossfilter direction set to Both, which makes perfect sense to me because all SKU columns contain unique values.
When I placed fields in a matrix I noticed a blank row was added, this tells me not all SKU’s from 90 and 365 are present in your MasterSku table. Upon closer inspection these items are missing in your master. Once you fix that, I hope it resolves your issue.
Thank you - this is a hand to the head moment. I’m so used to working on one to many relationships I didn’t really see the obvious that these particular files are one to one in both directions.
I’ll look at those MIA items.
Appreciate the set of eyes to see the obvious that I was missing.
gina
Hi @gkavesh,
Please know that I’m not suggesting to keep that crossfilter filter direction set to both, however I understand that the UI can’t determine otherwise… So unless there is a clear reason not to AND you understand the implications I would strongly suggest to change that into a single direction from your DIM to your FACT table(s).
Side note.
If you where to append your SKU90 and SKU365 with an attribute to differentiate between the two, the UI would have had no issue detecting the relationship or direction.
Example:
Sales90+365.csv (288.5 KB)
I hope this is helpful
Hmm - are you saying that by adding a new column as an attribute BI will be smart enough to recognize the relationship is one sided?
When I make the relationship 1 sided I’m forced into a 1 to many relationship within BI and that then results in the 90 & 365 data not able to coexist on the output.
On this version I’ve changed it one to one with both directions which gets me the output I’m looking for though with a bit of trepidation as these are 2 of the 8 datasets that I’m ultimately trying to bring together to replace a massive excel vlookup nightmare. Not all data sets will have all SKU’s which is why I pulled the ‘master’ sku listing as my ‘fact table’.
I’ve attached the raw xls data as well as I didn’t do that to begin with not realizing that was needed.
90DaySales.xlsx (81.7 KB)
365Sales.xlsx (196.1 KB)
FullSkulisting.xlsx (7.3 MB)
Replenishment Test.pbix (6.6 MB)
It’s not directly because of the attribute but of the duplication in SKU’s
(see the model on the right)
.
You’ve also created
implicit measures by dragging numerical fields onto the canvas. As a general best practice always create
explicit measures and hide all numerical columns. (see model on the right)
.
Now you can compare them separately OR side by side in a matrix
Here’s your sample file. (Just select the FileLocation from the list to restore the queries)
eDNA - Replenishment Test.pbix (12.8 MB)
I hope this is helpful.
Thank you-
The merging of sales the way you did is ‘new’ to me which is certainly educational.
Will continue plugging along
gina
Hello @gkavesh good to see you are having progress with your inquiry. 
Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
