cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SimonJacobs Regular Visitor
Regular Visitor

Relationships with multiple look up tables

Hi,

 

I'm having problems getting a dashboard to work that uses multiple look up tables due to limitations in relationships between tables.  I've seen the problem discussed but the solution (http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/) doesn't solve the problem for me.

 

I have a dashboard that uses multiple data tables - e.g. Facebook data, Twitter data, Research Data, Sales data.  I then have three look up tables

* a date table (e.g. so I can convert dates into Quarters for reporting)

* a country code look up table convert country codes (found in data files) to country names (more user-friendly for display): e.g. FR to France and

* a population size look up table storing the population figures for the countries - e.g. so I can put data into context - e.g. 6m Facebook fans in the UK = 10% of the population.

 

The problem comes when I create relationships with the look up tables because I can't have multiple relationships with the tables - e.g. If I connect the Facebook data file to the country look up and the date look up tables I can only connect the Twitter data table to either the country look up or the date table but not both as that would indirectly create two relationships between the Facebook File and the Twitter file via the look up files.

 

I've tried as suggested in one of the solutions to upload multiple versions of look up tables (e.g. country lookup 1, country lookup 2 etc.) so I can link each data source to a seprate one, however that doesn't work because I use slicers on the dashboard to select the time period and the countries to display.  If the relationship between the various data files isn't to the same look up file then the slicers will not control all charts on the dashboard.

 

Does anyone have an idea on how to solve this?

 

Many thanks

 

Simon

1 ACCEPTED SOLUTION

Accepted Solutions
austinsense Established Member
Established Member

Re: Relationships with multiple look up tables

I'm having a bit of trouble imagining what you're describing, but here's what I would suggest:

 

1. Power BI by default makes the relationships bi-directional, go ahead and turn that off on all the relationships - make them go only one direction.

2. You already know this but I have to say it - don't connect your lookup tables directly to each other and don't connect your data tables directly to each other.

 

But yeah take a little screenshot and that would help people help you.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

8 REPLIES 8
Moderator v-yuezhe-msft
Moderator

Re: Relationships with multiple look up tables

Hi @SimonJacobs,

It is difficult for us to provide effective method based on your description. Could you please post a snapshot of relationships among your tables? Also we need to know sample data of your tables and your expected result.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
SimonJacobs Regular Visitor
Regular Visitor

Re: Relationships with multiple look up tables

Hi Lydia,

 

Thank you for your reply.  Please find some imanges below.

Relationships: In the screenshot you can see 3 data sources - BPD Social Data-Quarterly, BPD Insight Data, BPD Audience Measurement.  In the data files country is represented by the 2 letter ISO code so the look up table converts this to the full name for display on charts.  In the image below you can see I've added a second country look up table to avoid joining BPD Insight Data to Country Codes which would create multiple relationships between the 2 data sources.  However the social data file contains more countries than the insight data file so I have a page filter set to filter the countries to those in the Insight data file. The problem is if there's not a releationship between the two files on country then it won't filter both data sets.

 

Similarly I have a slicer set up on year-quarter (shown as quarter or period in the image below).  It works for the social and insight data as those files have a relationship via quarters key, however it won't control charts based on the audience measurement source because there's no relationship - and to create one by settign up a relationship between Quarters Key and Audience Measurement would result in multiple relationships between Insight Data and Audience Measurement.  The other problem I have is that the data sources contain data for more than 1 brand.  At present I have a visual level filter on every visual set to a single brand.  ideally I'd like a single filter - either on the page or on a slicer to choose the brand to make it easier to switch between them.  However this would require even more multiple relationships.

Relationships2.png

Here you can see a sample of the data inthe BPD Audience measurement data source.

Data Sample.png

 

Below is a screenshot of the dashboard.  As I said really all data needs to be linked on quarter, brand and country.

Dashboard.png

 

Let me know if you need any more info and thanks again for taking the time to help!

 

Simon

austinsense Established Member
Established Member

Re: Relationships with multiple look up tables

I'm having a bit of trouble imagining what you're describing, but here's what I would suggest:

 

1. Power BI by default makes the relationships bi-directional, go ahead and turn that off on all the relationships - make them go only one direction.

2. You already know this but I have to say it - don't connect your lookup tables directly to each other and don't connect your data tables directly to each other.

 

But yeah take a little screenshot and that would help people help you.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂

View solution in original post

SimonJacobs Regular Visitor
Regular Visitor

Re: Relationships with multiple look up tables

Works like a charm, thank you!

Serdia Frequent Visitor
Frequent Visitor

Re: Relationships with multiple look up tables

"don't connect your lookup tables directly to each other and don't connect your data tables directly to each other." - Why is that?

Thank you 

bhrdrms Frequent Visitor
Frequent Visitor

Re: Relationships with multiple look up tables

@Serdia me too , curious about this information. Did you learn any things?

 

Best Regards,

CarlosValente Frequent Visitor
Frequent Visitor

Re: Relationships with multiple look up tables

Hi there , could you help me ? I'm having the some trouble .

 

I have a big hierarchy which is working fine .The hierarchy tables are linked like this hierarchy_1[nodeID] 1----->* hierarchy_2[parentID].

 

 

My problem is whenever I have a look up table  , I want to be able to link it with all of the hierarchy . I can't because of ambiguity between relationships . I already did everything you told to do .

 

powerhelp.pngpowerhelp.png

In case you didn't understood my question , I want to be able to link casefiles with all hierarchy tables without having ambiguity problems ..

 

CarlosValente Frequent Visitor
Frequent Visitor

Re: Relationships with multiple look up tables

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 159 members 1,709 guests
Please welcome our newest community members: