Forum Discussion
Helper IRelationships 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.
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
Helper IHi 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.
Here you can see a sample of the data inthe BPD Audience measurement data source.
Below is a screenshot of the dashboard. As I said really all data needs to be linked on quarter, brand and country.
Let me know if you need any more info and thanks again for taking the time to help!
Simon