Create a relationship between two data tables via linked lookup tables

[tj_horrocks]

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 Data and Benchmark Data. I want to be able to view the sector weights for both the account and its related benchmark in the same visual. Here is a simplified version of my data model:

Account Lookup holds the primary key for Account Data and Benchmark Lookup holds the primary key for Benchmark Data. The relationship between account and benchmark is stored in Account Data but each benchmark code can be used by multiple accounts so I cannot use BenchmarkCode within Account Data as a primary key.

If I create a relationship between Account Lookup and Benchmark Lookup as above I almost get what I need, but the total weight for the benchmark sector is not filtered, it should be 100:

(Besides I was told you should never create relationships between lookup tables...)

What is the solution?

Thanks, Tim

[tj_horrocks]

Thanks @v-cgao-msft 

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

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[tj_horrocks]

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]

You are welcome.

[v-cgao-msft]

Hi @tj_horrocks ,

Could you provide this sample file after removing the private data? 

How to provide sample data in the Power BI Forum

Is the [Sector] in the table from the 'Sector Lookup' table? Try change the cross-filter to both, like:

Measure = calculate([Benchmark Weight],crossfilter('Sector Lookup'[Sector Code],'Benchmark Data'[Sector Code],BOTH)

This could also be the reason, see the blog:

Why Power BI totals might seem inaccurate

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum