Multiple relationships between multiple tables

Question

Hi,&nbsp;I have an issue with relationships set in my dataset.&nbsp;I have 4 related tables Calendar, Sales Data, Brands, Forecast, with the below relationships:1.&nbsp; Calendar =&gt;&nbsp;Forecast (Date=Date)2.&nbsp;Calendar =&gt; Sales Data (Date=Date)3. Brands =&gt; Forecast (Brand Name=Brand Name)4.&nbsp;Brands &lt;=&gt; Sales Data (Brand Name1=Brand Name1)&nbsp;I have a visualization that shows Sales data and Forecast by Date and Brand Name.Now I'd like to add the filtering option for Sales Channel so when a Sales Channel is selected, both Forecast and Sales Data gets filtered. As a key field between Sales Data Table and Forecast I have Sales Channel.&nbsp;Any suggestions on how to create that will be highly appreciated.&nbsp;&nbsp;

amitchandak · Answer

Anonymous , if both tables have the sales channel, create a common table and use that to filter 
refer
https://www.youtube.com/watch?v=Bkf35Roman8

anonymous · Answer

HI&nbsp;amitchandak&nbsp;,&nbsp;Thanks for your response. the problem is taht the sales channel is a calculated column that is a result of another 2 tables. I have a common customer table where I've created a calculated column and identified the channel by customer. Now I have a "common" table that can relate as below with:1. Customer Table =&gt; Sales Table (Cutomer ID=Customer ID)2. Customer Table =&gt; Forecast Table (Sales Channel=Sales Channel)however I receive an error that the relationship cannot be set due to ambiguity that it creates..&nbsp;

anonymous · Answer

Hey&nbsp;Anonymous&nbsp;can you send a snippet of the structure of your tables ?A print screen that shows the columns in each table its more than fine

anonymous · Answer

Anonymous&nbsp;Below you can see the structure of my actual model. I'd like to link Forecast Table to Channel Table. As a primary key I have channel name in both tables. Whenever the channel filter will be applied to my visual, I'd like to see the Forecast shown per channel.I can create channel colum in Sales table also (if it will be required).&nbsp;Any suggestion can help.Thanks.

v-cazheng-msft · Answer

Hi,&nbsp;Anonymous&nbsp;You can create relationships for these tables like the following.&nbsp;&nbsp;The result looks like this:&nbsp;Here is the pbix.&nbsp;Best RegardsCaiyun Zheng&nbsp;Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.&nbsp;

Forum Discussion

Multiple relationships between multiple tables

7 Replies

Related content

MULTIPLE CARD

Date Relationships - Multiple Tables, Multiple dates.

Multiple dataViewMappings

Creating Multiple manage relationships at ones

Merge data from multiple excel files from multiple sharepoint folders