Forum Discussion

tj_horrocks's avatar
tj_horrocks
New Member
3 years ago
Solved

Create a relationship between two data tables via linked lookup tables

Hi,   Bit of a beginner here, I am sure this is very straightforward. Many thanks to anyone who takes the time to help.   I am trying to create a relationship between two data tables, Account Dat...
Need Help
tj_horrocks's avatar
tj_horrocks
New Member
3 years ago

Thanks Anonymous 

 

I uploaded the files to One Drive here, hope it works: Shared Files

 

This is all fabricated data, there is nothing private or confidential.

 

Yes, Sector in the table is coming from the Sector Lookup table. I tried creating a measure as below, not sure if I was doing it quite how you suggested:

 

WeightCalc = CALCULATE(SUM('Benchmark Data'[Weight]),CROSSFILTER('Sector Lookup'[Sector],'Benchmark Data'[Sector],BOTH))
 
This resulted in rows for all benchmarks being displayed, not just those for the benchmark associated to the account:

 

(Although I think this has helped me understand why the total is showing as 300 in the original benchmark weight column too.)

 

I tried playing around with other filter conditions but since there is no relationship between the Benchmark Data table and Account Lookup I could not figure out a way to apply the correct filter to BenchmarkCode.

 

Maybe I need a change to my data model to have a unique code which is a combination of AccountCode and BenchmarkCode? But this seems like it would create a lot of duplicate data.

 

Appreciate your help,

Tim

 

  • tj_horrocks's avatar
    tj_horrocks
    New Member
    3 years ago

    Brilliant! Thanks Ashish_Mathur

     

    So I create a many-to-many relationship between Account Lookup and Benchmark Data and then I can use that relationship in my Benchmark Weight measure.

     

    I'm not too sure on when it is good practice to use many-to-many relationships, I'll look into this.

     

    Thanks again, Tim

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      You are welcome.