Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SimonJacobs
Helper I
Helper I

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
austinsense
Impactful Individual
Impactful Individual

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
austinsense
Impactful Individual
Impactful Individual

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 🙂
Anonymous
Not applicable

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 ..

 

Anonymous
Not applicable

"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 

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

 

Best Regards,

Works like a charm, thank you!

v-yuezhe-msft
Employee
Employee

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.