cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thedeadzeds Helper II
Helper II

Help With Table Relationships

Hi Guys,

 

I am hoping someone can help me with the Power BI relationship.  I have 5 tables:

 

  1. dweekly – lookup table based on the dates from the Fleet_Weekly table
  2. Site – lookup table
  3. Brand – lookup table
  4. Sales_Weekly
  5. Fleet_weekly

See below

My relationships are as folows:

Dweekly table date = Sales Weekly Week Ending – Many to one and Both

Site table site = Sales Weekly Site - Many to one and Both

Brand table brand = Sales Weekly Brand - Many to one and Both

Brand table brand = Fleet Weekly Brand - Many to one and Both

 

POWER BI 1.png

 

I now have a report which looks like the below:


POWER BI 2.png

 

I can filter by Date and Brand and both sections in the graph will change accordingly which is great.  However, I also want to be able to filter by Site and for some reason I cannot get this to work properly.  If I try and create a relationship between the site table and site within the fleet weekly table, I get the following error message.  

 

POWER BI 3.png

Is there a way to be able to also filter by site?

Many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-xjiin-msft Solution Sage
Solution Sage

Re: Help With Table Relationships

Hi @thedeadzeds,

 

First you should know that in Power BI, you are only able to create one inactive relationship between two tables.

 

However since all the relationships you have set to Both Cross Filter Direction. It means there already exists an inactive relationship between Site and Brand. The path will be Site -> Sales Weekly -> Brand.

 

And now you want to create a new relationship between Site and Fleet Weekly, then a second relationship between Site and Brand will be generated as Site -> Fleet Weekly -> Brand. That's why the error occurs.

 

To resolve your issue, you can set the Cross Filter Direction to Single for the relationships " Site and Sales Weekly " and " Brand and Fleet Weekly ".

 

Thanks,
Xi Jin.

View solution in original post

1 REPLY 1
v-xjiin-msft Solution Sage
Solution Sage

Re: Help With Table Relationships

Hi @thedeadzeds,

 

First you should know that in Power BI, you are only able to create one inactive relationship between two tables.

 

However since all the relationships you have set to Both Cross Filter Direction. It means there already exists an inactive relationship between Site and Brand. The path will be Site -> Sales Weekly -> Brand.

 

And now you want to create a new relationship between Site and Fleet Weekly, then a second relationship between Site and Brand will be generated as Site -> Fleet Weekly -> Brand. That's why the error occurs.

 

To resolve your issue, you can set the Cross Filter Direction to Single for the relationships " Site and Sales Weekly " and " Brand and Fleet Weekly ".

 

Thanks,
Xi Jin.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors