Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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. 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        lbendlin
        Super User
        6 years ago

        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.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,

    Concatenate the two-column in both the tables and join

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

     

    and then join. It should work.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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.