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:   Responsibilities Table This table lists each "sales mana...
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.

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.