cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

Creating multiple relationships between two tables

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):

6-16 Sample.JPG

Desired Result (Region Asia and Product A):

6-16 Sample Desired.JPG

 

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

 

Thank you for your help. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Creating multiple relationships between two tables

Hi, @PowerBI2020 

 

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.

a1.png

 

a2.png

 

Best Regards

Allan

 

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

View solution in original post

5 REPLIES 5
Highlighted
Super User V
Super User V

Re: Creating multiple relationships between two tables

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

 

There might be other solutions too.

Highlighted
Helper IV
Helper IV

Re: Creating multiple relationships between two tables

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. 

Highlighted
Super User IX
Super User IX

Re: Creating multiple relationships between two tables

@PowerBI2020 ,

Concatenate the two-column in both the tables and join

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

 

and then join. It should work.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User V
Super User V

Re: Creating multiple relationships between two tables

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.

Highlighted
Community Support
Community Support

Re: Creating multiple relationships between two tables

Hi, @PowerBI2020 

 

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.

a1.png

 

a2.png

 

Best Regards

Allan

 

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors