Creating multiple relationships between two tables

[Anonymous]

Hello, 

I am trying to make a table visual of sales data that is filtered by a "sales manager" slicer. I currently have two tables:

1. Responsibilities Table
• This table lists each "sales manager" and their respective product, region and team responsibilities. Some managers only one responsibility while others have multiple responsibilities.
2. Sales Table
• This table lists each "sale" and its corresponding product, region and team. 

These tables are related by product, region and team. 

My requirement is that the sales table visual is filtered when a user selects a "sales manager" and then only the sales that correspond to the selected manager's responsibilties are visible. For example, Jim is responsible for Product A and Region Asia, so when Jim is selected, I would like the visual to only show sales that correspond to Product A and Region Asia. I am having trouble because you cannot create more than one relationship between two tables. Currently, when I select Jim my table visual is showing all sales that correspond to Region Asia, regardless of Product, because the only relationship that exists is Region --> Region. How would I go about creating this tri-relationship between two tables?

See below for my desired result as well as my current result, along with a sample dashboard. 

Current Result (All Region Asia, regardless of Product):

Desired Result (Region Asia and Product A):

Sample Dashboard: https://www.dropbox.com/s/agmlpph9vnw0oqq/6-16%20Sample.pbix?dl=0

Thank you for your help. 

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may create a measure as below. There is no relationships between two tables. If you want relationships, you may create another same table to create realtionships. The pbix file is attached in the end.

Visual Control = 
IF(
    NOT(ISFILTERED(Responsibilities[Manager])),
    1,
    IF(
        SELECTEDVALUE(Sales[Product]) in DISTINCT(Responsibilities[Product])&&
        SELECTEDVALUE(Sales[Region]) in DISTINCT(Responsibilities[Region]),
        1,0
    )
)

Then you need to put the measure in the visual level filter to filter the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

Concatenate the two-column in both the tables and join

Key = [Region] & "-" & [Team]

and then join. It should work.

[lbendlin]

Either normalize your data model, or use composite keys (for example a concatenation of sales manager and region)

There might be other solutions too.

[Anonymous]

Thank you for the reply. Can you elaborate on normalizing my data model? I'd rather not concatenate as the dataset is large and it would require a lot of memory. 

[lbendlin]

Normalization= moving dimension columns (stuff you filter by) out into their own dimension table, leaving only facts (stuff you do math on) and keys (lookups to the dimension) in the facts table.

Anything with low cardinality is probably a dimension.