Multiple relationships between multiple tables

[Anonymous]

Hi,

I have an issue with relationships set in my dataset.

I have 4 related tables Calendar, Sales Data, Brands, Forecast, with the below relationships:

1.  Calendar => Forecast (Date=Date)

2. Calendar => Sales Data (Date=Date)

3. Brands => Forecast (Brand Name=Brand Name)

4. Brands <=> Sales Data (Brand Name1=Brand Name1)

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.

Any suggestions on how to create that will be highly appreciated.

[v-cazheng-msft]

Hi, @Anonymous 

You can create relationships for these tables like the following.

The result looks like this:

Here is the pbix.

Best Regards

Caiyun Zheng

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.

[Anonymous]

Unfortunately this scenario is not possible for me as the dashboard is pretty big and a lot of data/calculated colums/measures will be impacted if the model will be changed. I'm thinking about a DAX calculation maybe?

Anyway, thank you very much for your input.

[v-cazheng-msft]

Hi, @Anonymous 

If so, you can consider doing calculation with TREATAS function which can create virtual relationship. And then use the result to create the visual.

Best Regards

Caiyun Zheng

[Anonymous]

Hey @Anonymous 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]

@Anonymous 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).

Any suggestion can help.

Thanks.

[amitchandak]

@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]

HI @amitchandak ,

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 => Sales Table (Cutomer ID=Customer ID)
2. Customer Table => Forecast Table (Sales Channel=Sales Channel)

however I receive an error that the relationship cannot be set due to ambiguity that it creates..