Complicated Relationship Thru Multiple Tables?

[PBINoob]

Relationship Diagram- https://imgur.com/3fmWWLU

I have a Date Filter/Slicer using Calendar[Date]. --> This works perfectly.
I have a Name Filter/Slicer using ASINList[Short Name]. --> This doesn't work.

I have a measure to calculate Gross Profits: 
Gross Profit =
SUMX(OrderFees, OrderFees[Gross Sales] + OrderFees[FBA Per Order Fulfillment Fee] + OrderFees[FBA Per Unit Fulfillment Fee] + OrderFees[FBA Weight Based Fee] + OrderFees[Fixed Closing Fee] + OrderFees[Variable Closing Fee] + OrderFees[Promotion Amount]
)

What I can tell, the Name Filter/Slicer isn't working because there's no direct relationship between ASINList and OrderFees tables (see relationship image above). When I try to connect one, it gives an error saying "You can't create a relationship between these two columns because one of the columns must have unique values."

So the way it sits now, ASINList[Short Name] would have to connect thru 2 tables (OrderItems & Orders tables) to get to the OrderFees table, and connect thru the SKU field... which, obviously it's not working.

I'm a NOOB to all this, but learning quick! I'd REALLY appreciate any/all help you can give!

THANK YOU!

[MFelix]

Hi @PBINoob,

Create a dimension table with unique values with the column that links the Orderitems and Order tables then make the relatioship between those two table through the new one and we will be abble to make the link.

You can use the query editor or dax to create this new table.

Regards
MFelix

[PBINoob]

Thank you for the reply!
Question: The Dim Table links the OrderItems and Orders tables? Or the OrderItems and the OrderFees tables? The OrderFees table is the one that I'm using to calculate the Gross Profits...

Also- the common column/fields in all of those is the Order ID. So use that one?

THANK YOU!

[PBINoob]

OK- I tried this-
https://imgur.com/LIZ1bnU

Created a new table with unique values using Order ID. 

I then tried to create a relationship between the OrderFees and ASINList- got the same error as before. 

I must be missing something- any ideas?

[MFelix]

Hi @PBINoob,

Looking at the image you send out and the information the use of the additional table will not remove the error since two fo your connections are inactive so you would need to use the USERRELATIONSHIP to calculate the measures you need.

Can you share a sample of the PBIX file with the first verison of your relaitonship model and an expect result?

Believe that the error can be context related in terms of the visuals.

Regards,

MFelix

[PBINoob]

Hi-

Thank you for the reply and helping me out. I REALLY appreciate it! 

https://www.dropbox.com/s/139ncwlxw01jm54/temp%20for%20OrderFees%20to%20SKU.pbix?dl=0

Basically, I'm trying to filter the Gross Sales by the ASINList[Short Name] field (bottom left bar chart in the visuals). It's giving me gross sales for each day, but that's with every item (Short Name) summed up... it's not filtering per Short Name- like we said, bc there's no relationship between ASINList table and the OrderFees table.

Please let me know what you think. 

[PBINoob]

Any chance you've been able to take a look at this? I'm really stuck and could use the help.
Thank you!