Complicated Relationship Thru Multiple Tables?

Question

Relationship Diagram-&nbsp;https://imgur.com/3fmWWLUI have a Date Filter/Slicer using Calendar[Date]. --&gt; This works perfectly.I have a Name Filter/Slicer using ASINList[Short Name]. --&gt; This doesn't work.I have a measure to calculate Gross Profits:&nbsp;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])&nbsp;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 &amp; 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 · Answer

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

pbinoob · Answer

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?&nbsp;THANK YOU!

pbinoob · Answer

OK- I tried this-https://imgur.com/LIZ1bnU&nbsp;Created a new table with unique values using Order ID.&nbsp;I then tried to create a relationship between the OrderFees and ASINList- got the same error as before.&nbsp;I must be missing something- any ideas?

mfelix · Answer

Hi&nbsp;PBINoob,&nbsp;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.&nbsp;Can you share a sample of the PBIX file with the first verison of your relaitonship model and an expect result?&nbsp;Believe that the error can be context related in terms of the visuals.&nbsp;Regards,MFelix

pbinoob · Answer

Hi-Thank you for the reply and helping me out. I REALLY appreciate it!&nbsp;https://www.dropbox.com/s/139ncwlxw01jm54/temp%20for%20OrderFees%20to%20SKU.pbix?dl=0&nbsp;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.&nbsp;Please let me know what you think.&nbsp;

Forum Discussion

Complicated Relationship Thru Multiple Tables?

6 Replies

Related content

Complicated IF

Relationship

Extracting complicated text from a column

Relationships

Relationship Issue