Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-alq-msft
Community Support
Community Support

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.

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
v-alq-msft
Community Support
Community Support

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.

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.

amitchandak
Super User
Super User

@Anonymous ,

Concatenate the two-column in both the tables and join

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

 

and then join. It should work.

lbendlin
Super User
Super User

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
Not applicable

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. 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.